Forum Discussion

DineshArivu's avatar
DineshArivu
Icon for Helper I rankHelper I
1 year ago
Solved

Rolling 3 months not working properly

Hi Experts,   I want to create a dax to display the last 3 months when we select any month from the slicer. if not selected then it will display all month data in clustered column chart. Monthname...
  • johnt75's avatar
    johnt75
    1 year ago

    You would need separate date tables for the visual and for the slicer. You can use the method described in the article I posted, or you could just have a disconnected copy of your date table which you use on the slicer, using the current date table in the visuals, and change the code to 

    Last 3 months =
VAR HasMonthSelected =
    ISFILTERED ( 'CustomCalendar for slicer'[MonthName] )
VAR EndDate =
    MAX ( 'CustomCalendar for slicer'[Date] )
VAR SelectedDate =
    MAX ( 'CustomCalendar'[Date] )
VAR Result =
    SWITCH (
        TRUE (),
        HasMonthSelected
            && SelectedDate IN DATESINPERIOD ( 'CustomCalendar'[Date], EndDate, -3, MONTH ), 1,
        HasMonthSelected, 0,
        1
    )
RETURN
    Result
FarhanJeelani's avatar
FarhanJeelani
Icon for Super User rankSuper User
1 year ago

Hi DineshArivu ,

The issue is that you’re comparing text MonthName values and doing a math operation on them. That won’t work and also doesn’t give you a clear “last 3 months when a month is selected, otherwise show all months” behavior.

 

You can use a real date dimension and apply a rolling-window filter only when a month is selected. 

 

Use a visual-level filter to show last 3 months only when a slicer is used

Create a simple measure that returns 1 for all rows when no slicer is active, and returns 1 only for the last 3 months when a slicer is active. Then filter the visual to show items where this measure = 1.
This keeps all months visible by default, and when you pick any month, the visual shows only the last 3 months.
DAX (ShowLast3IfSelected) VAR HasMonthSelected = ISFILTERED('Calendar'[MonthName]) VAR EndDate = MAX('Calendar'[Date]) VAR StartDate = EOMONTH(EndDate, -2) + 1 RETURN IF( HasMonthSelected, IF('Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate, 1, 0), 1 )

 

What to do:

Add this as a new measure ShowLast3IfSelected.
In the visual (the clustered column chart), add ShowLast3IfSelected to the Visual level Filters and set it to show items when the value is 1.
Keep your axis as MonthYear (text) and slicer on MonthName (text).


Notes:

This approach relies on a proper Date table (Calendar) with Date, MonthName, MonthYear, etc.

 

Please mark this post as solution if it helps you. Appreciate Kudos.