none
Trouble in calculated column over date range

    Question

  • I have two date columns in one table

    Coolumn Hears :  Activity|City|Start_Date|End_Date

    Row 1 : A1|C1|01/01/2014|05/01/2014

    Row 2 : A1|C1|06/01/2014|07/01/2014

    Row 3:  A2|C2|06/01/2014|07/01/2014

    Row 4:  A3|C3|03/01/2014|04/01/2014

    Expected output like - If user selects date range 02/01/2014 to 07/01/2014

    Column Header

    City | #StartCount| #EndCount

    Row 1 : C1   | 1          | 2   

    Row 2 : C2   | 1          | 1

    Row 2 : C3   | 1          | 1  

    Here #StartCount is
    - Need to grouped by City.
    - Its Count of Distinct Activity
    - It should consider date boundries as : All activity for which start date 'greater or equeal(>=)' Input Start date and (less than '<') End Date

    Here #EndCount is
    - Need to grouped by City.
    - Its Count of Distinct Activity
    - It should consider date boundries as : All activity for which Ends date 'Less or equeal(<=)' Input End date and (Greater than '>') End Date

    Could you please suggest me expression to be used for such case.
    Calculated measure or dax can be used..
    Friday, February 28, 2014 8:01 AM

Answers

  • try this one as a DAX calculated measure - not as a calculated column:

    MyMeasure:=CALCULATE(
    DISTINCTCOUNT('Table'[Activity]),
    FILTER(
        'Table',
        'Table'[StartDate] <= MAX('Input'[Date]) && 'Table[EndDate] > MIN('Input'[Date])
    )
    )

    you may vary MIN/MAX or >/< as I am not quite sure what you actually want to achieve

    'Input' would be a separate table only used to select your time-range but not directly linked to your fact-table

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, April 22, 2014 12:21 PM
    Answerer

All replies

  • Sandip,

    Any luck with this?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, April 21, 2014 5:15 AM
    Owner
  • try this one as a DAX calculated measure - not as a calculated column:

    MyMeasure:=CALCULATE(
    DISTINCTCOUNT('Table'[Activity]),
    FILTER(
        'Table',
        'Table'[StartDate] <= MAX('Input'[Date]) && 'Table[EndDate] > MIN('Input'[Date])
    )
    )

    you may vary MIN/MAX or >/< as I am not quite sure what you actually want to achieve

    'Input' would be a separate table only used to select your time-range but not directly linked to your fact-table

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, April 22, 2014 12:21 PM
    Answerer