Single Date Slicer To Connect With A Window Of Dates

[Anonymous]

Hi All,

I am working on a company report and have chosen to run this through PBI for ease of deployment. What I am hoping to achieve is having a single date selection slicer that will connect to a range of dates effective/expiration dates in one of our rate tables. In a perfect world a user would enter a date of 7/23/2021 (Todays Date) or another date in the past and it would bring up the corosponding rate that was used over that period. If anyone has reccomendations I would appreciate the assistance.

[BA_Pete · Accepted solution]

Hi @Anonymous ,

This is actually quite a big question as there's quite a few variables in play.

My first instinct would be to say that you should relate your calendar table to all of the dates in your rates table and activate the relationship you want to us in any given measure by using USERELATIONSHIP(...). This will allow you to use a single date slicer to control the different outouts required (which role-playing calendar dimensions wouldn't do).

However, I strongly suspect that your rates table is, in fact, a Slowly-Changing Dimension (SCD) table. In which case there's two ways to handle your scenario:

1) Expand your SCD into individual dates that run consecutively so you have a specific date and rate for every day that you can relate your calendar table to.

2) Write your SCD date ranges into your measures using DAX, so your measure identifies SELECTEDVALUE(calendar[date]) from your slicer, then picks up the correct rate from the rates table by comparing the rate dates to the SELECTEDVALUE e.g. 

_rate =
CALCULATE(
  MAX(ratesTable[rate]),
  FILTER(
    ratesTable,
    ratesTable[rateStartDate] <= SELECTEDVALUE(calendar[date])
    && ratesTable[rateEndDate] >= SELECTEDVALUE(calendar[date])
  )
)

This isn't really a working example, but you get the picture.

Pete

[selimovd]

Hey @Anonymous ,

that is absolutely possible and best practice.

Create a date table and use that as slicer. Then connect the date table to your other tables and only use the date table to slice. Like this you get the result you want.

Here is a small tutorial how to create a date table:

https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic