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.
Dear Dax Gurus,
Would appreciate some help - I'm looking to create a calculated column forecast which at index 0 simply takes the (Activity + (Activity * Activity Change %)), but for all other indexes takes the previous row calculated forecast and applies the current row activity change %.
Note the index resets at each change in (Cost Centre)"Code".
Screenshot 1
Sample Data
Month Year | Code | Activity Change % | Activity | Index | Forecast |
| Formula |
01/03/2022 | 100 | 2.0% | 3048 | 0 | 3108 |
| =D2+(D2*C2) |
01/04/2022 | 100 | -4.0% |
| 1 | 2984 |
| =F2+(F2*C3) |
01/05/2022 | 100 | 5.0% |
| 2 | 3134 |
|
|
01/06/2022 | 100 | 7.0% |
| 3 | 3353 |
|
|
01/07/2022 | 100 | -4.0% |
| 4 | 3219 |
|
|
01/08/2022 | 100 | 8.0% |
| 5 | 3476 |
|
|
01/09/2022 | 100 | -3.0% |
| 6 | 3371 |
|
|
01/10/2022 | 100 | 2.0% |
| 7 | 3439 |
|
|
01/11/2022 | 100 | 4.0% |
| 8 | 3576 |
|
|
01/12/2022 | 100 | -6.0% |
| 9 | 3363 |
|
|
01/03/2022 | 200 | 1.7% | 3448 | 0 | 3506 |
| =D12+(D12*C12) |
01/04/2022 | 200 | -3.4% |
| 1 | 3387 |
| =F12+(F12*C13) |
01/05/2022 | 200 | 4.2% |
| 2 | 3530 |
|
|
01/06/2022 | 200 | 5.9% |
| 3 | 3739 |
|
|
01/07/2022 | 200 | -3.4% |
| 4 | 3611 |
|
|
01/08/2022 | 200 | 6.8% |
| 5 | 3857 |
|
|
01/09/2022 | 200 | -2.6% |
| 6 | 3757 |
|
|
01/10/2022 | 200 | 1.7% |
| 7 | 3822 |
|
|
01/11/2022 | 200 | 3.4% |
| 8 | 3952 |
|
|
01/12/2022 | 200 | -5.2% |
| 9 | 3749 |
|
|
I did try Gerhard Brueckl's interesting approach for creating this entirely in DAX Link but unfortunately I couldn't get this to work at Cost Centre Code level (works perfectly at an overall level) so have gone down the path of creating the above table instead
Thanks in advance
Adam
Solved! Go to Solution.
Try a new column,
Value = MAXX(filter(Data, [Index] =0 && [Code] =EARLIER(Data[Code])),[Activity])* PRODUCTX(Filter(DATA, [Code] =EARLIER(Data[Code]) && [Index] <=EARLIER(Data[Index])), 1+[Activity Change %])
File attached after signature
Try a new column,
Value = MAXX(filter(Data, [Index] =0 && [Code] =EARLIER(Data[Code])),[Activity])* PRODUCTX(Filter(DATA, [Code] =EARLIER(Data[Code]) && [Index] <=EARLIER(Data[Index])), 1+[Activity Change %])
File attached after signature
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |