A few weeks ago a requirement came through to a build a Power BI report that would allow the Report User to analyse the variance in pricing of hotel stays over a given time period.
In the Data Warehouse there resides a "time span" fact table that behaves similarly to a Type 2 Slowly Changing Dimension table. Basically, this fact table inserts a new row every time there is a change in pricing recorded against a particular hotel for a particular Check in Date in the future. When a change in pricing is recorded a new record is created and the Valid From and Valid To dates are set accordingly as is shown in the screenshot below.
This is telling the Report User that between the times of 2021-06-04 02:43:14 and 2021-06-07 22:16:43, the price of the given hotel was $1,886.73 if the Check In Date was 09/07/2021.
At 2021-06-07 22:16:43 the price of the same hotel for the same Check In Date jumped to $3,575.32 and this is the current price.
The request was to have a Slicer on the report that would allow the Report User to select a date and then have the pricing returned that was valid as that date for a given Check In Date (Hotels and Check In Dates filters come from additional slicers).
To solve this scenario, I created a measure with the following DAX expression where the 'Hotel Pricings' is my fact table and 'Date' [Valid On] is used for my Date Slicer.
The Expression will return a value of 1 if no 'Date' [Valid On] value is selected by the Report User via the Slicer, and it will also return a value of 1 if the selected 'Date' [Valid On] is between the Valid From and Valid To timespan range of the row. The SUMX ensures the measures iterates through every row in the 'Hotel Pricings' table in the current Row and Filter contexts set in the Power BI report table visual.
Now all I have to do is use this Measure as a Visual Filter on my table in the report and set it to only show items that have a value of 1.
The Slicer looks like this:
If the Report User selects 09/06/2021 00:00:00 they want to see the pricing that was valid for the Check In Dates of 09/07/2021 if they were to book on that date (09/09/2021)
With the Slicer selected you can now see that only the row where 09/06/2021 falls between the Valid From and Valid To date of the row is returned.
Hey presto - it works!
The row where the timespan range was between 2021-06-04 02:43:14 and 2021-06-07 22:16:43 is not returned as the Date chosen does not fall into this period.
Hope this helps if you are faced with the same scenario!