Calculate cumulative values for selected months

[NidhiBhusari]

Hi All,

Here is the scenario where i want to calculate total value for month selected. ALso the values of previous 2/3 months should get calculated. Ex- if i select April from slicer then in the matrix it should cummulative sum of february, March and April.

Can anyone please help me with the DAX?

[Anonymous · Accepted solution]

Hi @NidhiBhusari ,

Please use the following formula to create a measure and then apply it to visual-level filter, set as "is 1".

Flag =
VAR _sele =
    DATE ( SELECTEDVALUE ( 'ForSlicer'[Date].[Year] ), SELECTEDVALUE ( 'ForSlicer'[Date].[MonthNo] ), 1 )
VAR _date =
    DATE ( MAX ( 'Table'[Date].[Year] ), MAX ( 'Table'[Date].[MonthNo] ), 1 )
RETURN
    IF ( DATEDIFF ( _date, _sele, MONTH ) IN { 0, 1, 2 }, 1 )

But based on my test, it seems that the selection in slicer could not related to Columns field in Matrix, so I'd suggest you put Company to Columns field and Date(Year and Month) to Rows field in matrix:'

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@NidhiBhusari , A measure like example with help from date table

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

[NidhiBhusari]

Hi @amitchandak , i tried implementing this but is not giving expected output. Can you please suggest any other way to achieve this?