Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to Solution.
@TheAmazingPBI , Create a new column like
sumx(filter(Table, [FY] = earlier([FY]) && [Unit] = earlier([unit]) && [Period] <= earlier([Period])),[PBIT])
@TheAmazingPBI , Create a new column like
sumx(filter(Table, [FY] = earlier([FY]) && [Unit] = earlier([unit]) && [Period] <= earlier([Period])),[PBIT])
thank you heaps, would have never found that function myself
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
12 | |
12 | |
12 |