Custom predefined Date Ranges

Hi all,
I’ve created Dax and created a table to show custom date range slicer on my report like (this week, last week, last month, this month, Last6months) and link it to the normal calendar date table but it is not filtering the calendar date table as expected, any ideas to create such custom ranges and link it to the calendar date table?
I used suggestions from the power bi community forum here : custom predefined table

Thank you,
Jojo

@jojo,

Check out @JarrettM’ post in this thread. I think it will get you exactly what you need.

  • Brian

Hi Brian,
Thank you for the quick update. I couldn’t find the PBIX shared in the forum, can you share the link for that.

@jojo,

It’s attached to this post, but I’m not seeing the custom date range slicer.

However, I absolutely know I’ve seen a file from @JarrettM where he has this feature implemented.

@JarrettM - sorry, can’t locate the relevant file at the moment. Would you mind posting your custom date range slicer file again in this thread? Thanks!

  • Brian

Is this the post you’re looking for?

@Greg @jojo @BrianJ,

@Greg just posted the link that shows how to do this.

Thanks
Jarrett

Thank you!

based on the discussion above I tried to slightly modify the M query and add additional row on the slicer like “previous 6 months” but Power query complains as it is out of range! any ideas?

{“Previous 6 Months”, Date.AddMonths( Date.From(Date.StartOfMonth(TodaysDate)), -1), Date.AddMonths( Date.From(Date.EndOfMonth(TodaysDate)), -6), 10}

This works thanks!
{“Previous 6 Months”, Date.AddMonths( Date.From(Date.StartOfMonth(TodaysDate)), -6), Date.AddMonths( Date.From(Date.EndOfMonth(TodaysDate)), -1), 10}

1 Like

It’s great to know that you are making progress with your query @jojo. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

@Greg @BrianJ

The M code canned dynamic date range slicer seems to break if both the date range slicer and default continuous date slicer are used in the same report.

Hi @jojo. Please post your PBIX and we’ll have a look. Greg

This is the same pbix file I previously got from this forum. when I try to select on the date slicer and then get back to the dynamic date range slicer, it does not reset back the date even if the bidirectional filter is on between period and date tables.It was supposed to take today date as initial input but that is not the case.

EDNA Executive Sales Report- Jarrett Moore.pbix (930.9 KB)

You’re right … having both a [Dates] slicer and a [Period] slicer on the same page can lead to some unexpected behaviour … I’d try to keep only 1 per page.

If you have a specific implementation you’re trying to create, please attach your work-in-progress PBIX file along with a description of what’s not working, and a mockup and description of exactly what your looking for, and the forum members can pursue further.
Greg

@Greg is correct in only using one, but I use button on my reports that switches from the period slicer to the regular date range slicer. Great idea for a YouTube video. I’ll do a video sometime in the near future with this technique.

Thanks
Jarrett

2 Likes