Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kamala
Regular Visitor

Get data of 3 months ago by current month

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%...

 

 

 

1 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:

 

 

89.PNG

 

To calculate your MAPE, your measure can be like:

 

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

Regards,

 

View solution in original post

3 REPLIES 3
Kamala
Regular Visitor

we have to create measure "xMAPE" using DAX  

@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:

 

 

89.PNG

 

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
Regular Visitor

image001 (1).jpg

The above screen shot give clearly expalination

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.