Forum Discussion

NidhiBhusari's avatar
NidhiBhusari
Icon for Helper IV rankHelper IV
5 years ago
Solved

Calculate cumulative values for selected months

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?


 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      NidhiBhusari
      Icon for Helper IV rankHelper IV
      5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.