Rolling 3 months not working properly

[DineshArivu]

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,

        1,

        0

    )

 please help me on this.

Thanks

DK

[v-lgarikapat]

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

[johnt75]

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

[DineshArivu]

@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( ..

[johnt75]

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

[DineshArivu]

@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.

[johnt75 · Accepted solution]

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

[DineshArivu]

@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.

[johnt75]

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

[DineshArivu]

Works now , thanks @johnt75 and all 🙂

[FarhanJeelani]

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.

[Selva-Salimi]

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?

[DineshArivu]

@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 .