Looking for Sumif help to create cumulative value based on value in a row

[TheAmazingPBI]

Hi all

Thanks for taking the time to read. I've tried looking at SUMIF solutions from this forum but unable to find a solution for my problem. I haven't been able to get it working in Excel and was hoping with the DAX formulas this might be possible.

The objective is to create a calculated column that provides a cumulative total for PBIT. Open to other ways to calculate this, my thoughts are that there are 3 criteria [Unit], [FY] and only sum rows where [period] <= of the Period in current row.

e.g. in row 2 (period 202002) PBIT should be 6 as Unit = A, FY = 2020, rows 1 and 2 are <=202002.

I have tried a CALCULATE(SUM(Table[PBIT]),[FY],[Unit]) and it gives me an error, no idea how I can add in the expression <=Period for that row. Hoping someone can assist. Thank you

Col A	Col B	Col C	Col D	Col E
Unit	Period	FY	PBIT	PBIT YTD
A	202001	2020	4
A	202002	2020	2	This is what I am trying to calculate (should be 6)
A	202003	2020	5
A	202004	2020	3
A	202005	2020	6
B	202001	2020	7
B	202002	2020	8	This should be 15
B	202003	2020	3
B	202004	2020	6
B	202005	2020	1

[amitchandak · Accepted solution]

@TheAmazingPBI , Create a new column like

sumx(filter(Table, [FY] = earlier([FY]) && [Unit] = earlier([unit]) && [Period] <= earlier([Period])),[PBIT])

[TheAmazingPBI]

thank you heaps, would have never found that function myself