Show correct totals in forecast

[basrooz]

Hi,

I have a data model that automatically refreshes each morning with yesterdays data. I've created a measure that's returning a forecast based on the actuals and the data from recent years.

I now want to calculate the total forecast until end of this year. I've used the following simple DAX formula for that. If the date yesterday or earlier it should pick up the actuals, otherwise it should pick up the forecast.

IF ( 
    MIN ( 'Date'[Date] ) < ( TODAY() - 1) , 
    [# Actuals] , 
    [# Forecast] 
    )

As a result, this is my data for this year (the numbers are exaggurated to show what's going wrong):

2022 month	Result	forecast or actual
Jan	50	actual
Feb	50	actual
Mar	50	actual
Apr	50	actual
May	50	actual
Jun	50	actual + forecast
Jul	100	forecast
Aug	100	forecast
Sep	100	forecast
Oct	100	forecast
Nov	100	forecast
Dec	100	forecast
Total	300

The problem lies in the total for the year: it is only showing the total of the actuals (300), and not the total of the actuals and the forecast combined (it should return 900).

How can i change my formula so that the totals always represent a combination of the total and the actuals?

Thanks!
Bas

[ALLUREAN · Accepted solution]

Hi, @basrooz 

You can check my article and hope it will help you

https://allure-analytics.com/index.php/2022/06/11/combine-actuals-forecast-in-power-bi/

[basrooz]

That's the one, thank you very much @ALLUREAN !

[SpartaBI]

@basrooz you need somthing like:

SUMX(
	VALUES('Date Table'[Date]),
	IF ( 
	    MIN ( 'Date'[Date] ) < ( TODAY() - 1) , 
	    [# Actuals] , 
	    [# Forecast] 
    )
)

      

Showcase Report – Contoso By SpartaBI

[basrooz]

Hi @SpartaBI ,

Thanks for the quick response, but this doesn't do the trick unfortunately. the forecast numbers are highly impacted and the total is still showing the total of the actuals, not of the actuals and the forecast.

[SpartaBI]

@basrooz amm..so I need to see the file in this case 🙂

[basrooz]

@SpartaBI  I cannot share it unfortunately.

All I can add is that the problem is in calculating the total. In the formula it's saying that if the time is less than today, it should pick the actuals. If you look at the total, then the time frame is 2022.01.01 - 2022.12.31 . This time frame have days that are less than today, hence it's only calculating the total of the actuals for this.

So somehow the formula should say that it should perhaps split up the total between a before today and an after today timeframe, and add those two numbers up... I can't seem to think of a good solution to it...

[SpartaBI]

@basrooz ok, let's try this 🙂 Watch this video:
https://www.youtube.com/watch?v=DKgF-5QHY68
This is the best source for DAX 🙂
Let me know if it solved your issue.