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.
Good afternoon All,
Thank you for your help. I've have been trying to replicate my company's accuracy measure in Power BI for a single column dataset. We measure accuracy against our forecast from several lags (Prior Month, Two Months ago, Three Months ago, etc.)
I have succeeded in recreating it however it required that I build the measure up through a series of 5 Calculated Measures. Is there any way to reduce this number as when I need to add different accuracy measures, multiplying each one by 5 will get very messy.
Step 1
Filter Forecast Date for Actuals:
Step 5
Create accuracy measure:
Link to Power BI Example:
Link to Fruit Accuracy Demo File
Regards,
Gareth.
Solved! Go to Solution.
Hi @jazking123 ,
We can use this measure to meet your requirement:
New Accuracy Calculation =
VAR current_month =
MONTH ( TODAY () )
VAR prior_month_value =
CALCULATE (
SUM ( 'Sheet1'[Value] ),
FILTER ( Sheet1, MONTH ( 'Sheet1'[Date] ) = current_month - 1 )
)
VAR prior_month_Actual =
CALCULATE (
SUM ( 'Sheet1'[Value] ),
FILTER ( Sheet1, Sheet1[Forecast Date] IN { "Actual" } ),
ALL ( Sheet1 ),
MONTH ( Sheet1[Date] ) = current_month - 1
)
VAR prior_Actual_for_All =
CALCULATE (
CALCULATE (
SUM ( 'Sheet1'[Value] ),
FILTER ( Sheet1, Sheet1[Forecast Date] IN { "Actual" } ),
ALL ( Sheet1 ),
MONTH ( Sheet1[Date] ) = current_month - 1
),
Sheet1[Forecast Date] IN { "Actual" }
)
RETURN
1
- (
ABS ( prior_month_value - prior_Actual_for_All ) / prior_Actual_for_All
)
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Hi @jazking123 ,
We can use this measure to meet your requirement:
New Accuracy Calculation =
VAR current_month =
MONTH ( TODAY () )
VAR prior_month_value =
CALCULATE (
SUM ( 'Sheet1'[Value] ),
FILTER ( Sheet1, MONTH ( 'Sheet1'[Date] ) = current_month - 1 )
)
VAR prior_month_Actual =
CALCULATE (
SUM ( 'Sheet1'[Value] ),
FILTER ( Sheet1, Sheet1[Forecast Date] IN { "Actual" } ),
ALL ( Sheet1 ),
MONTH ( Sheet1[Date] ) = current_month - 1
)
VAR prior_Actual_for_All =
CALCULATE (
CALCULATE (
SUM ( 'Sheet1'[Value] ),
FILTER ( Sheet1, Sheet1[Forecast Date] IN { "Actual" } ),
ALL ( Sheet1 ),
MONTH ( Sheet1[Date] ) = current_month - 1
),
Sheet1[Forecast Date] IN { "Actual" }
)
RETURN
1
- (
ABS ( prior_month_value - prior_Actual_for_All ) / prior_Actual_for_All
)
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Thank you @v-lid-msft!!!
This works perfectly. I have a few modifications to make, but just using multple vars was the key.
Gareth.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |