Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
I am trying to build a dynamic measure in DAX that uses an "Actuals" measure for all historical months up to the last completed month, and uses a "Forecast" measure for the current month forwards. I'm currently using TODAY() as the date, which is therefore pulling through some actuals data for the current month (September in the example below) and adding it to the September forecast, therefore overstating the "Actuals & Forecast" value for the current month. In my simple example below, the September Act&Fcast should be $23 (i.e. ignoring the partial actuals month of $4) and only using the forecasted $23, but my DAX is giving the answer of $27, being the sum of the Actuals ($4) and Forecast ($23) for the current month of September.
Here's the current DAX:
Should Say | Is Currently Saying | |||
Actuals | Forecast | Act&Fcast | ||
Jan | $23 | $23 | $23 | |
Feb | $21 | $21 | $21 | |
Mar | $22 | $22 | $22 | |
Apr | $24 | $24 | $24 | |
May | $26 | $26 | $26 | |
Jun | $28 | $28 | $28 | |
Jul | $21 | $21 | $21 | |
Aug | $21 | $21 | $21 | |
Sep | $4 | $23 | $23 | $27 |
Oct | $24 | $24 | $24 | |
Nov | $24 | $24 | $24 | |
Dec | $25 | $25 | $25 |
Solved! Go to Solution.
Hi @Tiddlerontheroo ,
According to your description, if the value of Forecast column is empty, Act&Fcast column will output the value of Actuals column, if the value of Forecast column is not empty, Act&Fcast column will output the value of Forecast column.
Then you can create a measure.
Act&Forecast =
VAR LastMth =
CALCULATE(
TODAY(),
REMOVEFILTERS()
)
VAR Actuals = CALCULATE([Actuals])
VAR FutureForecast =
CALCULATE([Forecast)],
FILTER(_Calendar,
_Calendar[Date]> LastMth
)
)
VAR Result = IF(FutureForecast <>BLANK(),FutureForecast,Actuals)
RETURN
Result
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, I would be very helpful if you help me with the dax formula for Forcast column. Thank you.
Hi @Tiddlerontheroo
Please follow my proposed solution for this How to frame L3M moving average logic for the futu... - Microsoft Power BI Community
Hi @Tiddlerontheroo ,
According to your description, if the value of Forecast column is empty, Act&Fcast column will output the value of Actuals column, if the value of Forecast column is not empty, Act&Fcast column will output the value of Forecast column.
Then you can create a measure.
Act&Forecast =
VAR LastMth =
CALCULATE(
TODAY(),
REMOVEFILTERS()
)
VAR Actuals = CALCULATE([Actuals])
VAR FutureForecast =
CALCULATE([Forecast)],
FILTER(_Calendar,
_Calendar[Date]> LastMth
)
)
VAR Result = IF(FutureForecast <>BLANK(),FutureForecast,Actuals)
RETURN
Result
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
57 | |
21 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |