Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi smart people,
I have issues with a calculations and need your help.
I’m trying to do a “total + forecast” measure (in the picture called Total).
Where I have actuals I will show the actual and where I have a forecast I will show the calculated forecast (total).
The problem is that I have the “delta increase” only, as a separate column, per period and I cannot figure out how to calculate the “running total” based on the previous months calculated value.
I the picture I need to take:
2022-05: 3734+202 = 3936 (actual + forecast=total)
2022-06: 3936+72 = 4008 (total + forecast=total)
Etc
Etc
When I have actuals for 2022-05 that will be the total.
Any ideas are welcome.
Data I have | Data I have | Data I have | Data I need to calculate |
Period | Actual | Forecast Increase | Total |
2022-01 | 3651 |
| 3651 |
2022-02 | 3688 |
| 3688 |
2022-03 | 3710 |
| 3710 |
2022-04 | 3734 |
| 3734 |
2022-05 |
| 202 | 3936 |
2022-06 |
| 72 | 4008 |
2022-07 |
| 17 | 4025 |
2022-08 |
| 10 | 4035 |
2022-09 |
| 17 | 4052 |
2022-10 |
| 1 | 4053 |
2022-11 |
| 0 | 4053 |
2022-12 |
| 1 | 4054 |
Solved! Go to Solution.
Try these measures. I made the Period column a date by adding the first day of the month (2022-01 becomes 2022-01-01). This will enable you to create a relationship with the date table. Alternatively, you could create a Period Number column with values like 202201, and use Period Number instead of Period in the Running Total measure.
Actual = SUM ( FactTable[Actual] )
Forecast = SUM ( FactTable[Forecast] )
Running Total =
VAR vLastActual =
CALCULATE (
LASTNONBLANKVALUE ( FactTable[Actual], [Actual] ),
ALLSELECTED ( FactTable )
)
VAR vResult =
IF (
MAX ( FactTable[Actual] ) <> BLANK (),
[Actual],
vLastActual
+ CALCULATE (
[Forecast],
FILTER (
ALLSELECTED ( FactTable ),
FactTable[Period] <= MAX ( FactTable[Period] )
)
)
)
RETURN
vResult
Proud to be a Super User!
Try this measure. I adjusted the DAX to handle months where both Actual and Forecast exist.
Running Total =
VAR vLastActual =
CALCULATE (
LASTNONBLANKVALUE ( Actual[Actual Value], [ActualValue] ),
ALLSELECTED ( Actual )
)
VAR vLastActualDate =
CALCULATE (
LASTNONBLANK ( 'Date'[Date], [ActualValue] ),
ALLSELECTED ( Actual )
)
VAR vResult =
IF (
MAX ( Actual[Actual Value] ) <> BLANK (),
[ActualValue],
vLastActual
+ CALCULATE (
[ForecastValue],
FILTER (
ALLSELECTED ( 'Date' ),
'Date'[Date] <= MAX ( 'Date'[Date] )
&& 'Date'[Date] > vLastActualDate
)
)
)
RETURN
vResult
Now you can take that vacation. 🙂
Proud to be a Super User!
Hi @DataInsights
When I tried on my "real" file I got the last actual value as the 2022-11 value.
So I started to prepare a dummy file to upload (hope the link works) and then I acutally got the 2022-10 value in 2022-11. BUT the calculation took into account all previous forcast values where I have actuals.
I need a vacation 🙂
Try this measure. I adjusted the DAX to handle months where both Actual and Forecast exist.
Running Total =
VAR vLastActual =
CALCULATE (
LASTNONBLANKVALUE ( Actual[Actual Value], [ActualValue] ),
ALLSELECTED ( Actual )
)
VAR vLastActualDate =
CALCULATE (
LASTNONBLANK ( 'Date'[Date], [ActualValue] ),
ALLSELECTED ( Actual )
)
VAR vResult =
IF (
MAX ( Actual[Actual Value] ) <> BLANK (),
[ActualValue],
vLastActual
+ CALCULATE (
[ForecastValue],
FILTER (
ALLSELECTED ( 'Date' ),
'Date'[Date] <= MAX ( 'Date'[Date] )
&& 'Date'[Date] > vLastActualDate
)
)
)
RETURN
vResult
Now you can take that vacation. 🙂
Proud to be a Super User!
@DataInsights What can I say but thank you!
Thank you so much for thaking the time. This was exactly what I needed.
Glad to hear that worked!
Proud to be a Super User!
Try these measures. I made the Period column a date by adding the first day of the month (2022-01 becomes 2022-01-01). This will enable you to create a relationship with the date table. Alternatively, you could create a Period Number column with values like 202201, and use Period Number instead of Period in the Running Total measure.
Actual = SUM ( FactTable[Actual] )
Forecast = SUM ( FactTable[Forecast] )
Running Total =
VAR vLastActual =
CALCULATE (
LASTNONBLANKVALUE ( FactTable[Actual], [Actual] ),
ALLSELECTED ( FactTable )
)
VAR vResult =
IF (
MAX ( FactTable[Actual] ) <> BLANK (),
[Actual],
vLastActual
+ CALCULATE (
[Forecast],
FILTER (
ALLSELECTED ( FactTable ),
FactTable[Period] <= MAX ( FactTable[Period] )
)
)
)
RETURN
vResult
Proud to be a Super User!
@DataInsights
Worked like a charm. Thank you.
One question. If e.g. 2022-11 is a blank / null value and not zero in the forecast.
How can I obtain the same result, mening 2022-11 = 4053.
Glad to hear that worked. I changed the 2022-11 forecast to blank/null and got the same result. Are you getting a different result?
Proud to be a Super User!