Get data of 3 months ago by current month

[Kamala]

The business has decided to compare original forecast 3 months ago to “Current Months” CM_Orders_Actual.  This measurement will give us a better understand on how our forecast is impacting our suppliers.  Goal is to have a MAPE < 20%...

[Kamala]

we have to create measure "xMAPE" using DAX  

[v-sihou-msft · Accepted solution]

@Kamala

In this scenario, you just need to limit the context to the same day three months ago. Please keep all RecordDate into the first day of month since you have 2nd for all Feb. To calculate the ForcastCore3 month Ago, you can use formula like below:

ForcastCore3 3 Months Ago=
CALCULATE (
    SUM ( 'Table'[ForcastCore3] ),
    FIRSTDATE (
        DATESINPERIOD (
            'Table'[RecordDate],
            LASTDATE ( 'Table'[RecordDate] ),
            -4,
            MONTH
        )
    )
)

Please refer to my sample below:

To calculate your MAPE, your measure can be like:

MAPE =
 ( SUM ( 'Table'[CM_Orders_Actual] ) - [ForcastCore3 3 Months Ago] )
    / [ForcastCore3 3 Months Ago]

Regards,

[Kamala]

The above screen shot give clearly expalination