Forum Discussion
Helper IVRolling 12 month calender
Hi All....
I have a requirement, where i have to restrict the users to select only 12 rolling months in the slicer. I.e If the starting date selection is Jul-24 I can go as far back as Aug - 23 or as far forward as Jun -25.... can someone help me how i can do it.
Hi jostnachs
Create a column in your dates/calendar table that identifies what rolling month the current row date is. Then filter your slicer using that column. If the date being compared in DATEDIFF are the same, DATEDIFF will return 0 thus <=12 in the screenshot below.
If you want the latest date to be of month 0, use this calc column below.Rolling 13 Months = DATEDIFF ( CalendarTable[Date], MAX ( CalendarTable[Date] ), MONTH ) <= 12Thanks for the reply from danextian and anmolmalviya05, please allow me to provide another insight.
Hi jostnachs ,
Please refer to the following steps.Create two slicers of type After and Before.
They are respectively used as the start date and the end date.Edit the interaction effects of the two slicers.
Make the Start Date slicer can filter the End Date slicer.
Creates a measure for restricting the range of dates.Measure = VAR startDate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date])) VAR endDate = EDATE(startDate,11)+1 RETURN IF(MAX('Calendar'[Date])<endDate,1,0)
Add the measure to the End Date slicer's visual level filter, set to is 1.In this way, the max date selectable by the End Date slicer dynamically maintains a 12-month difference from the Start Date slicer selection date.
Please see the attahced pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- danextian
Super User
Hi jostnachs
Create a column in your dates/calendar table that identifies what rolling month the current row date is. Then filter your slicer using that column. If the date being compared in DATEDIFF are the same, DATEDIFF will return 0 thus <=12 in the screenshot below.
If you want the latest date to be of month 0, use this calc column below.Rolling 13 Months = DATEDIFF ( CalendarTable[Date], MAX ( CalendarTable[Date] ), MONTH ) <= 12 - anmolmalviya05
Hi , I hope you are doing good!
Create a Measure to Restrict Data Display: Define a measure to dynamically filter data based on the slicer selection:DateInRange =
VAR SelectedStartDate = MIN('DateTable'[Date])
VAR SelectedEndDate = EDATE(SelectedStartDate, 11)
RETURN
IF(MAX('DateTable'[Date]) >= SelectedStartDate && MAX('DateTable'[Date]) <= SelectedEndDate, 1, 0)
Apply the Measure to Visual Filters: Add this measure to the visual-level or page-level filters, and set it to = 1.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in
Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner
Thanks for the reply from danextian and anmolmalviya05, please allow me to provide another insight.
Hi jostnachs ,
Please refer to the following steps.Create two slicers of type After and Before.
They are respectively used as the start date and the end date.Edit the interaction effects of the two slicers.
Make the Start Date slicer can filter the End Date slicer.
Creates a measure for restricting the range of dates.Measure = VAR startDate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date])) VAR endDate = EDATE(startDate,11)+1 RETURN IF(MAX('Calendar'[Date])<endDate,1,0)
Add the measure to the End Date slicer's visual level filter, set to is 1.In this way, the max date selectable by the End Date slicer dynamically maintains a 12-month difference from the Start Date slicer selection date.
Please see the attahced pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
