Forum Discussion

jazking123's avatar
jazking123
Frequent Visitor
6 years ago
Solved

Create Accuracy Measure based on Single Column Data

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:

Actuals = Calculate(SUM('Sheet1'[Value]),
    'Sheet1'[Forecast Date] IN { "ACTUAL" })
 
Step 2
Filter the actuals to just the prior month (Otherwise it sums all the actuals):
Prior Month Actual = var current_month= MONTH(TODAY()) return  Calculate(SUM('Sheet1'[Value]),FILTER(Sheet1,Sheet1[Forecast Date] IN { "Actual" }),ALL(Sheet1),MONTH(Sheet1[Date])=current_month -1)
 
Step 3
Get the Actuals to show against the other forecast periods:
Prior Month Actual for All = Calculate('Sheet1'[Prior Month Actual],
    'Sheet1'[Forecast Date] IN { "Actual" })
 
Step 4
Get the prior month values for the other forecasts:
Prior Month Value = var current_month= MONTH(TODAY()) return  CALCULATE(SUM('Sheet1'[Value]),FILTER(Sheet1,MONTH('Sheet1'[Date])=current_month -1))

 

Step 5 

Create accuracy measure:

Accuracy Calculation = (1-(ABS([Prior Month Value]-[Prior Month Actual for All])/[Prior Month Value]))
 
Any help to even reduce even one of these steps would be really appreciated.
 

Link to Power BI Example:

Link to Fruit Accuracy Demo File 

 

Regards,

 

Gareth.

 

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

     

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,

     

     

    • jazking123's avatar
      jazking123
      Frequent Visitor
      6 years ago

      Thank you v-lid-msft!!!
      This works perfectly. I have a few modifications to make, but just using multple vars was the key.

       

      Gareth.