Dynamic Date Range Slicer- Query M

Power Query M Code Showcase
8

@JarrettM I love this dynamic date range slice, very useful, Thanks.

1 Like
9

how does this related to the creating of the date table @Melissa at the start of setting up the date table?

thanks Keith

10

Hi @Keith,

Have you looked over the article by Chris Webb in the first post of this thread?

The Dynamic Date Ranges you create are actually used to filter the Date table.
If you use the nested version, it will generate the Period table ‘automatically’. There’s no need to pass any actual parameters because they are all relative to TodaysDate = Date.From(DateTimeZone.FixedUtcNow()) and defined within the “Ranges”.

Where a Date table is required in most Models a Period table is optional.

I hope this is helpful.

11

Hi @Melissa,

No, i didn’t see it but i’ll have a look at.
thanks
Keith

13

Hi @cbchow,
Please start a new topic as this string has a solution to it.

thanks
Keith

14

Will do, thanks: Dynamic Date Range Slicer conflicting with outside period references

15

All,

Just did a video on YouTube that should be posted in the near future. Here is the M code that I referenced from that video:

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"All Dates",MinDates,MaxDates, 1},  
                {"Yesterday", Date.AddDays(TodaysDate,-1), Date.AddDays(TodaysDate,-1), 2},      
                {"Today", TodaysDate, TodaysDate, 3},
                {"5D", Date.AddDays(TodaysDate, -4), TodaysDate, 4}, 
                {"15D", Date.AddDays(TodaysDate, -14), TodaysDate, 5}, 
                {"1M", Date.AddMonths(TodaysDate, -1), TodaysDate, 6},   
                {"3M", Date.AddMonths(TodaysDate, -3), TodaysDate, 7},   
                {"6M", Date.AddMonths(TodaysDate, -6), TodaysDate, 8},        
                {"YTD", Date.From(Date.StartOfYear(TodaysDate)), TodaysDate, 9},
                {"1Y", Date.AddYears(TodaysDate, -1), TodaysDate, 10},  
                {"2Y", Date.AddYears(TodaysDate, -2), TodaysDate, 11}                  
                },
    fxCreatePeriodTabe = ( PeriodName as text, StartDate as date, EndDate as date, SortOrder as number ) as table =>
        let
            DayCount = Duration.Days(EndDate-StartDate)+1,
            DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
            AddPeriodName = List.Transform(DateList, each {PeriodName,_,SortOrder}),
            CreateTable = #table( type table[Period=text, Date=date, Sort=number], AddPeriodName)
        in
            CreateTable,
    
    GetTables = List.Transform(Ranges, each fxCreatePeriodTabe(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)
in
    Output

The PBIX file shown in the video can be found on this thread on the forum:

Here is the post from that thread that contains the PBIX used in the video:

image
image789×824 72.4 KB

Thanks
Jarrett

5 Likes
16

Hello everyone.
I have another question. When period selected through this slicer, no time intelligence measures are working. For example, Same Period Last Year returns blank.
Does anyone now a way to implement it? The idea is to select, for example This Week and see data for Same Period Last Week on Card or Visual.

Thank you

17

@skydancer,

Welcome to the forum – great to have you here! If you can please post a copy of your PBIX file, that will be a big help in diagnosing the problem. I suspect the issue mail lie with your data model, since I’ve used @JarrettM’s posted code for this date range slicer multiple times in past reports, and never had any problem with it.

  • Brian
18

Hi @skydancer. Without a PBIX its hard to say, but it sounds to me like you’re trying to access time intelligence calculations that span outside of the range selected in the “period” slicer; if so, the [Dates] table is restricted to the selected period (range) and that may be the root of your issue.
Greg

19

what about multiple dates? does this require a default relationship?

20

Thank you :slight_smile: Also happy to be here from now on :))

Here is a pbix.Demo Report for Enterprise DNA.pbix (2.4 MB)

21

@skydancer,

@Greg was right in his previous post regarding trying to access time intelligence calculations that span out of the selected date range. With a little DAX, you can work around this. Let me know if this works for you:

 Sales LY Test =
CALCULATE (
    SUM ( FactSales[Amount NET] ),
    DATEADD ( 'Date'[Date], -1, YEAR ),
    ALL ( DateRelativePeriods[Period] )
)

Here is a photo of what it looks like:

image
image1136×729 47.8 KB

I have a YouTube video coming out towards the end of this week on the Enterprise DNA YouTube Channel on the Period Table. I’ll make sure I do a follow up video soon on how to handle situations like these with the Period Table.

Thanks
Jarrett

1 Like
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: