Dynamic Date Range Slicer- Query M

Power Query M Code Showcase
22

Amazing! It works :grin:
Thank you very much. But I do not really understand why. My thinking is that when I am removing filter from Cross filtered table (Relative Period), it keeps the currently selected (by slicer) dates and at the same time gives access to whole date table. And my understanding was that I need to keep fitlter on Dates from RelativePeriod so that it can get access to other rows of Date Table, because while doing Date calculations (Marked as Date), Power BI remove filters from this table. If it’s not a big pain, can you please explain what exactly happening with Power BI and filters?
P.S. I am sorry if this is stupid. I am learning PowerBI and Dax for about 3 months only…

In any case, thank you for your help. I already see that membership is a money well spent :slight_smile:

23

@skydancer,

Not a stupid question at all! So when you are using the “Period” in the slicer, and you have it interacting with any of your tables, it will override any time intelligence calculation you have, unless you do something similar to what I did with your measure. I used the ALL function, which in the simplest of terms means ignore the slicer and give me the results of Sales LY regardless of what the slicer is telling it to do.

For more on the ALL function, go to this site within Enterprise DNA ( I would spend a bunch of time in here going over the different functions):

https://info.enterprisedna.co/dax-function-guide/all/

Thanks
Jarrett

2 Likes
24

Hi Melissa,

Great work, I really love the Period Table! I just have one problem, when I want to add the option of selecting a custom date range as well.

I added a ‘Custom date’ option to the period table (giving me all dates for the latest 3 years):

{"Custom date", Date.From(Date.AddYears(Date.StartOfYear(TodaysDate), -3)), TodaysDate, 17}

I added a ‘Date slicer’ to be used when “Custom date” is selected:

image

The problem is, If you choose your custom date range to be anything from the minimum, and then go and select another period from the Periods table, then the dates inside the Date Slicer will stay unchanged.

It filters the data correctly, but it is a bit confusing for the users that the ‘Date slicer’ is still showing the previously selected date range.

Do you know is there is a way for the “Date Slicer” to reset when a period is selected?

25

Hi @JNordentoft,

Welcome to the forum!
Would you please create a new topic for your question and include a sample PBIX?

Thank you.

26

Hi @JNordentoft.

BI Elite presented a solution not dissimilar to your request a while ago on YouTube, where they hide the Date slicer slider when the custom period is not selected. Perhaps this would help.

Greg

1 Like
27

Hi Greg,

I implemented BI Elite’s solution to Melissa’s Period table, and it work as he shows is the video you linked to.

The problem is still, when you go to ‘Custom’, and set a custom date range, and then go and chose for example ‘MTD’ from the ‘Date Period’ then the ‘Date Slicer’ is still showing those dates you previously selected.

His solution only works if you “clear” the Custom date range before you select another Date Period :slight_smile:

28

Hi Melissa,

Thanks a lot!
I will create a new topic for this question :slight_smile: