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 fr...
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,

     

     

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.