Getting a value from a date slicer

[MajorBloodnock]

Hi, all; I hope you can help me.

I have had a good trawl round both on this forum and t'Interweb in general but can't find an answer to my question. I have a "between" date slicer based on the date values of a data table populated by records for every working day. As a result, the table does not contain any records for weekends or bank holidays. Typically the slicer is used to filter for a month's data and the first/last dates may not actually exist in the data (if 1st is a Sunday, for instance). I want to be able to programmatically access the dates the user selected, therefore, not the earliest/latest dates in the data that fall inside the slicer's filter.

Is this possible? I can't imagine I'm the first to want to do this.

[MajorBloodnock]

Thanks, Owen

Yes, that's what I was starting to expect; that I'd have to fill in dummy dates in one respect or another and that accessing a simple control property wouldn't be possible. It's rather heavy-handed but that's a Microsoft problem rather than yours so I've accepted your post as the solution.

Thanks for taking the time.

[OwenAuger · Accepted solution]

Hi @MajorBloodnock 

In general, you should create a Date dimension table containing a contiguous range of dates.

You should then create a 1:many relationship between the Date table and fact tables in your model.

Any date filters should then be applied via the Date table (rather than fact tables), such as your "between" slicer, and you will be able to access the earliest/latest dates selected in the slicer by referencing the Date table, independent of dates that actually exist in your fact table(s).

Here is one article in Microsoft Learn related to this:

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Regards