Cumulative Year to Date with multiple categories

[qquestel]

Hi,

I have a table with data for multiple employees who recieve bonuses on a monthly basis. I want to calculate the Cumulative Year to Date for each employee and display this in a table column. How do I get the cumulative YTD for each specific employee in the YTD column. I also need to get a summary for Each employee with the YTD for the given year as seen in the second table.

Employee	BonusDate	Bonus	YTD
Jane	11/1/2016	100	100
Sam	11/1/2016	120	120
Kevin	11/1/2016	130	130
Jane	12/1/2016	105	205
Sam	12/1/2016	125	245
Kevin	12/1/2016	135	165
Jane	1/1/2017	100	100
Sam	1/1/2017	120	120
Kevin	1/1/2017	130	130
Jane	2/1/2017	105	205
Sam	2/1/2017	125	245
Kevin	2/1/2017	135	265
Jane	3/1/2017	100	305
Sam	3/1/2017	120	365
Kevin	3/1/2017	130	395

Year	Employee	Bonus YTD
2016	Jane	205
2016	Sam	245
2016	Kevin	165
2017	Jane	305
2017	Sam	365
2017	Kevin	395

[v-ljerr-msft · Accepted solution]

Hi @qquestel,

Based on my test, you should be able to use the formula below to the cumulative YTD for each specific employee in the YTD column.

YTD = 
CALCULATE (
    SUM ( Table1[Bonus] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Employee] = EARLIER ( Table1[Employee] )
            && Table1[BonusDate] <= EARLIER ( Table1[BonusDate] )
            && YEAR ( Table1[BonusDate] ) = YEAR ( EARLIER ( Table1[BonusDate] ) )
    )
)

To get a summary for Each employee with the YTD for the given year, you can firstly add Year column in your table with the formula below.

Year = YEAR(Table1[BonusDate])

Then you should be able to use the formula below to create a new calculate table to get the expected result.

Table = 
SUMMARIZE (
    Table1,
    Table1[Year],
    Table1[Employee],
    "Bonus YTD", MAX ( Table1[YTD] )
)

Here is sample pbix file for your reference. 

Regards