Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Reference Sliced date in expression

I need to create a measure that, as part of its calculation, knows what the date range from the date range slicer is?   Each row of data is date and time stamped so I can reeference that, but bette...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi Anonymous,

    If you add Table[Date] in slicer, and you select a date range in slicer, you can create a measure using formula below to get the latest date of the range in slicer.

    Latest Date in slicer =
CALCULATE ( LASTDATE ( Table[Date] ), ALLSELECTED ( Table ) )


    Then you can use the meausre [Latest Date in slicer] in another DAX expression.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

If you add Table[Date] in slicer, and you select a date range in slicer, you can create a measure using formula below to get the latest date of the range in slicer.

Latest Date in slicer =
CALCULATE ( LASTDATE ( Table[Date] ), ALLSELECTED ( Table ) )


Then you can use the meausre [Latest Date in slicer] in another DAX expression.

Best Regards,
Angelia

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    v-huizhn-msft

     

    This works! Thank you for the suggestion.

     

    What I don't understand is, why the need for creating a separate table? Why doesn't it work when I reference [Date] in my main table of data for both the slicer and 'Latest Date is slicer' expression?

     

    The problem I now have is that the Slicer no longer affects the content of my report (because it's referencing another table). I could fix this with a relationship (many to one and one way?)

    However...

    The LASTDATE function insists on have distinct values, which my data does not have.

     

    The new, separate [Date].date table is generated with a distinct function, which solves the LASTDATE issue, but now means the data in that table doesn't reference my date ranges I have against the data (which is DD:MM:YY HH:MM:SS)

     

    How do I get the slicer to affect the column from which I'm reading Latest Date and affect my main data set?