Forum Discussion

AM_VRSResearch's avatar
AM_VRSResearch
Frequent Visitor
4 years ago
Solved

Cumulative Sum over multiple columns

I am trying to make a cumulative sum column that calculates the cumulative sum based off of an employees name, and the year.  So Alice and Bob may have values for 2020 and 2021 that I would like to c...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    AM_VRSResearch , Based on what I got

    Try like

    CALCULATE(
    SUM('Table'[TotalByMonth]),
    FILTER(
    ALLSELECTED('Table'[Year|Employee], 'Table'[Employee], 'Table'[Year]),
    ISONORAFTER('Table'[Year|Director], MAX('Table'[Year|Employee]), DESC) && 'Table'[Employee] =max('Table'[Employee])
    )
    )

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

amitchandak's avatar
amitchandak
Super User
4 years ago

AM_VRSResearch , Based on what I got

Try like

CALCULATE(
SUM('Table'[TotalByMonth]),
FILTER(
ALLSELECTED('Table'[Year|Employee], 'Table'[Employee], 'Table'[Year]),
ISONORAFTER('Table'[Year|Director], MAX('Table'[Year|Employee]), DESC) && 'Table'[Employee] =max('Table'[Employee])
)
)

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • AM_VRSResearch's avatar
    AM_VRSResearch
    Frequent Visitor
    4 years ago

    This was the answer I was looking for. Thank you.