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
  • DineshArivu's avatar
    DineshArivu
    Icon for Helper I rankHelper I
    1 year 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
      Icon for Super User rankSuper User
      1 year 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
      • DineshArivu's avatar
        DineshArivu
        Icon for Helper I rankHelper I
        1 year ago

        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.