Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Running Total Reset based on Column Attribute

Hello everyone   I have read numearous articles/posts regarding running total or cumulative total, but couldn't find anything as to how to reset the running total at the beginning of each year.   ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Anonymous,

     

    If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

    Cumulative_Net Revenue =
VAR currentYear =
    YEAR ( MAX ( 'DateKey'[Date] ) )
VAR currentMonth =
    MONTH ( MAX ( 'DateKey'[Date] ) )
RETURN
    CALCULATE (
        SUM ( 'Project Details_USD'[MTD Net Revenue] ),
        FILTER (
            ALL ( 'DateKey' ),
            YEAR ( 'DateKey'[Date] ) = currentYear
                && MONTH ( 'DateKey'[Date] ) <= currentMonth
        )
    )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

Cumulative_Net Revenue =
VAR currentYear =
    YEAR ( MAX ( 'DateKey'[Date] ) )
VAR currentMonth =
    MONTH ( MAX ( 'DateKey'[Date] ) )
RETURN
    CALCULATE (
        SUM ( 'Project Details_USD'[MTD Net Revenue] ),
        FILTER (
            ALL ( 'DateKey' ),
            YEAR ( 'DateKey'[Date] ) = currentYear
                && MONTH ( 'DateKey'[Date] ) <= currentMonth
        )
    )

 

Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thank you very much ... This formula generated the "expected" chart.

    Appeciate your time and response on this :)

    Petek