Urgent Help Needed: DAX Cumulative Value - Exclude Future Months

[Srinivas904]

Hi,

I am using a cumulative formula to calculate volume, as shown in the screenshot below. It is working fine, but I need a small change—I don’t want the cumulative values to be shown for future months.

This is the measure I am using. Can anyone please help me with this? Also, just to clarify, we are using a 4-4-5 calendar.

Cumulative YTD QTY =

VAR SelectedYear = SELECTEDVALUE(DIM_TIME[FIN_YEAR])  -- Get the selected year

RETURN

CALCULATE(

    [Volume_V1],

    FILTER(

        ALL(DIM_TIME),

        DIM_TIME[Full_Date] <= MAX(DIM_TIME[Full_Date]) &&

        DIM_TIME[FIN_YEAR] = SelectedYear  -- Restrict to the selected year

    )

)

Thanks

Srinivas

[johnt75 · Accepted solution]

Add a column to your date table to flag whether the month is the current month or before. You could do this in Power Query or DAX, sample DAX is

Current Month or Before =
DIM_TIME[Full_Date] <= EOMONTH ( TODAY (), 0 )

You can then modify your measure to

Cumulative YTD QTY =
IF (
    SELECTEDVALUE ( 'DIM_TIME'[Current Month or Before] ),
    VAR SelectedYear =
        SELECTEDVALUE ( DIM_TIME[FIN_YEAR] ) -- Get the selected year
    RETURN
        CALCULATE (
            [Volume_V1],
            FILTER (
                ALL ( DIM_TIME ),
                DIM_TIME[Full_Date] <= MAX ( DIM_TIME[Full_Date] )
                    && DIM_TIME[FIN_YEAR] = SelectedYear -- Restrict to the selected year
            )
        )
)

[Srinivas904]

Hi @johnt75 , this is working perfectly as i want, thank you so much. I really appreciate your quick help on this.