Forum Discussion

DineshArivu's avatar
DineshArivu
Helper I
11 months 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 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

  • johnt75's avatar
    johnt75
    11 months 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

12 Replies

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    11 months ago

    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's avatar
      DineshArivu
      Helper I
      11 months ago

      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 .

       

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    11 months 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.

    • DineshArivu's avatar
      DineshArivu
      Helper I
      11 months ago

      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's avatar
        johnt75
        Super User
        11 months ago

        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
  • v-lgarikapat's avatar
    v-lgarikapat
    Community Support
    11 months ago

    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