topic Re: Rolling 3 months not working properly in DAX Commands and Tips

Rolling 3 months not working properly

DineshArivu — Mon, 08 Sep 2025 15:46:44 GMT

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 is a slicer (MonthName = FORMAT([Date],"MMMM")) //text type
Monthyear is a X axis in visual (MonthYear = FORMAT([Date], "MMM-yyyy")) //text type

Tried this measure but no luck:

Last3Months =

VAR SelectedMonth = MAX(CustomCalendar[MonthName])

RETURN

IF(

SELECTEDVALUE(CustomCalendar[MonthName]) >= SelectedMonth - 2 &&

SELECTEDVALUE(CustomCalendar[MonthName]) <= SelectedMonth,

)

please help me on this.

Thanks

Re: Rolling 3 months not working properly

Selva-Salimi — Mon, 08 Sep 2025 16:21:49 GMT

Hi DineshArivu

Are x-axis and the slicer selected from the same table? Are they dimdate table or same table as your data? And also to make it clear, do you want to show the attached image if nothing selected on slicer and if selected 3 month rolling amount?

Re: Rolling 3 months not working properly

DineshArivu — Mon, 08 Sep 2025 16:38:08 GMT

Selva-Salimi yes Selva ..

Monthname & MonthYear is from same table.
do you want to show the attached image if nothing selected on slicer and if selected 3 month rolling amount? -- yes .

Re: Rolling 3 months not working properly

FarhanJeelani — Tue, 09 Sep 2025 05:19:43 GMT

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.

Re: Rolling 3 months not working properly

johnt75 — Tue, 09 Sep 2025 10:05:46 GMT

You could use the technique described in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/

Re: Rolling 3 months not working properly

DineshArivu — Tue, 09 Sep 2025 10:45:19 GMT

johnt75 hey john,

we are using data everything from a measure (Y axis), not from fact table.
Calendar table (Date table) is not visible when trying to apply IF condition in the dax.
FarhanJeelani
Selva-Salimi

Last3Months =

VAR HasMonthSelected = ISFILTERED('CustomCalendar'[MonthName])

VAR EndDate = MAX('CustomCalendar'[MonthName])

VAR StartDate = EOMONTH(EndDate, -2) + 1

RETURN

IF(HasMonthSelected, IF( ..

Re: Rolling 3 months not working properly

johnt75 — Tue, 09 Sep 2025 10:58:05 GMT

You can't work with the MAX of a month name, you could try

Last 3 months = VAR HasMonthSelected = ISFILTERED ( 'CustomCalendar'[MonthName] ) VAR EndDate = CALCULATE ( MAX ( 'CustomCalendar'[Date] ), ALLSELECTED ( 'CustomCalendar' ) ) VAR SelectedDate = MAX ( 'CustomCalendar'[Date] ) VAR Result = SWITCH ( TRUE (), HasMonthSelected && SelectedDate IN DATESINPERIOD ( 'CustomCalendar'[Date], EndDate, -3, MONTH ), 1, HasMonthSelected, 0, 1 ) RETURN Result

Re: Rolling 3 months not working properly

DineshArivu — Tue, 09 Sep 2025 11:08:23 GMT

johnt75 thanks john, i have applied this dax measure in visual filter and set 1 ,
but nothing reflects when select any month from the slicer.
still it is displaying as it is normally - selected month data only , not last 3 months.

Re: Rolling 3 months not working properly

johnt75 — Tue, 09 Sep 2025 11:15:16 GMT

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

Re: Rolling 3 months not working properly

v-lgarikapat — Wed, 10 Sep 2025 09:16:37 GMT

Hi DineshArivu ,

Thanks for reaching out to the Microsoft fabric community forum.

johnt75 , FarhanJeelani , Selva-Salimi ,

Thanks for your prompt response

DineshArivu ,

I wanted to follow up and confirm whether you’ve had the opportunity to review the information provided by johnt75 , FarhanJeelani , Selva-Salimi If you have any questions or need further clarification, please don’t hesitate to reach out.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
Lakshmi

Re: Rolling 3 months not working properly

DineshArivu — Wed, 10 Sep 2025 11:32:04 GMT

johnt75 thanks John, it is working fine in the part of rolling 3 months.

one more issue now is that the each month count is giving a total count, instead of each month count 😞

selected Aug month from the disconnected table slicer.

all the 5 bars are a individual measures.

MonthName slicer is from disconnected table
year slicer is from disconnected table
MonthYear X axis is from disconnected table
Y axis all from measures (5 measures we have added as per chart you can see) , each measures we never used any month related calculations.

Re: Rolling 3 months not working properly

johnt75 — Wed, 10 Sep 2025 12:36:33 GMT

The X-axis should come from the date table which is connected to your model, not from the disconnected one.

Re: Rolling 3 months not working properly

DineshArivu — Wed, 10 Sep 2025 14:16:02 GMT

Works now , thanks johnt75 and all 🙂