Compare Report Dates to Static Date Field

[Anonymous]

I'm new to Power BI and looking to create a report that compares multiple date fields against a static date.  The fields I am looking to use are Work Request #, Initiated Date (the date the item was initiated) and Repair Date (date it was repaired).  On a given date I want to know how many work requests are outstanding.  In the example below on 1/3/2019 there would be 3 open WRs (WR#1,2 and 3), 1/4/2019 there would be 1 (WR# 1) and 1/8/2019 there would be 0.   I can't figure out how to link to a static date.

WR#	Init Date	Repair Date
1	1/1/2019	1/8/2019
2	1/2/2019	1/4/2019
3	1/3/2019	1/4/2019
4	1/5/2019	1/7/2019
5	1/6/2017	1/7/2019

Thank you for your help!

[parry2k · Accepted solution]

@Anonymous you can easily achieve this by using following DAX. Add calendar table in your model and use it for slicer, don't create relatonship with this table and add following DAX to get open work orders based on selected date (if more than one date is selected then it will use MAX date)

# of Open WR = 
VAR __selectedDate = MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
    COUNTROWS ( 'Table (4)' ),
    
    'Table (4)'[Init Date] <= __selectedDate ,
    'Table (4)'[Repair Date] > __selectedDate ||
    'Table (4)'[Repair Date] == BLANK() 
)

[Anonymous]

@parry2k this is great and so easy to do!  Thank you for quick reply!!

[amitchandak]

You can connect a date dimension to different dates. One of the joins will be active others will be inactive. In the formula, you can activate based on need.

Please refer and see how to use userelation to change the date.

https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

@amitchandak thank you for the response!  I think @parry2k response worked better for what I'm trying to accomplish, but your post will help me with another report I'm working on!!