Forum Discussion

tariktik's avatar
tariktik
New Member
3 years ago
Solved

Using date slicer on a measure

First post here for Power BI newbie.   I have a table with a "Start Date", "End Date" columns among many other columns and I created a column to compute the duration between those two dates.   On...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi tariktik 

     

    I recommend that you create a date table in the model to have continuous dates and create a relationship from this date table's "Date" column to the "Start Date" column in the original table. Use date table's "Date" column in your slicer. This could avoid a scenario that some dates do not exist in the "Start Date" column in the original table, which would make the filter result not accurate. 

    Date = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))

    Then you can create the following measure

    Flag = IF(SELECTEDVALUE('Table'[End Date]) <= MAX('Date'[Date]), 1, 0)

    Add this measure on the table visual as a filter and set it to show items when value is 1. This will exclude the records you don't want from the table visual. 

    The sample file is attached at bottom. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

Hi tariktik 

 

I recommend that you create a date table in the model to have continuous dates and create a relationship from this date table's "Date" column to the "Start Date" column in the original table. Use date table's "Date" column in your slicer. This could avoid a scenario that some dates do not exist in the "Start Date" column in the original table, which would make the filter result not accurate. 

Date = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))

Then you can create the following measure

Flag = IF(SELECTEDVALUE('Table'[End Date]) <= MAX('Date'[Date]), 1, 0)

Add this measure on the table visual as a filter and set it to show items when value is 1. This will exclude the records you don't want from the table visual. 

The sample file is attached at bottom. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

  • tariktik's avatar
    tariktik
    New Member
    3 years ago

    Thank you very much v-jingzhang.