Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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 |
---|---|
12 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
27 | |
19 | |
14 | |
11 | |
7 |