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 calculate seperately.  Right now my formula is

 

TotalByMonth running total in Period|Year|Director = 
CALCULATE(
	SUM('Table'[TotalByMonth]),
	FILTER(
		ALLSELECTED('Table'[Year|Employee], 'Table'[Employee], 'Table'[Year]),
		ISONORAFTER('Table'[Year|Director], MAX('Table'[Year|Employee]), DESC)
	)
)

 But the issue I am having is that slicers do not seem to impact the year, only the Employee name.

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.

2 Replies

  • 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.