Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |