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,
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
Solved! Go to 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/
Proud to be a Super User!
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/
Proud to be a Super User!
@basrooz you need somthing like:
SUMX(
VALUES('Date Table'[Date]),
IF (
MIN ( 'Date'[Date] ) < ( TODAY() - 1) ,
[# Actuals] ,
[# Forecast]
)
)
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 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...
@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.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |