cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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
1 ACCEPTED SOLUTION
Super User

@TheAmazingPBI , Create a new column like

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

2 REPLIES 2
Super User

@TheAmazingPBI , Create a new column like

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

Regular Visitor

thank you heaps, would have never found that function myself