Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date filter across multiple different date fields

Hi,   I have data in a table like below:    Unique ID Status 1 Start Date Status 1 End Date Status 2 End Date Status 3 End Date Status 1 Time Status 2 Time Status 3 Time AA 4/29/202...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Create an unrelated calendar table as slicer then create measure like this:

    Count_1 = CALCULATE(COUNT('Table'[Unique ID]),FILTER('Table','Table'[Status 1 End Date] in VALUES('Table 2'[Date])&&'Table'[Status 1 Start Date]<=MAX('Table 2'[Date])))

    Sample .pbix 

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , One way is to unpivot this table https://radacad.com/pivot-and-unpivot-with-power-bi

 

Second way is to join all dates to a common date table and use userelation as per need. Only one can be active at a time.

Refer how to use: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/