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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
mmarshalek
Frequent Visitor

Calculation of 2 values different dates

MAPE.PNG

I've been working on this for about 5 days now,...  and really stuck...  Could use help...  I've attached my Power BI table..

 

Trying to subtract "ForecastCore3" from CM_Ship Actuals...

Need to use Current Month for "CM_Ship_Actuals" and current month - 3 for "ForecastCore3"

 

Is this possible in DAX?

 

Thanks,

Mike

 

 

6 REPLIES 6
mmarshalek
Frequent Visitor

Thank you! I've tried what you suggested however still now lining correctly....  I had created a calendar table and no success..  Below is the output from my adjustments and I have also include more detailed..

 

 

More detail:Results.jpgmape.jpg

Thanks again!

@mmarshalek

 

Please try to create a calcuated column with following formula.

 

MAPE =
VAR ValueThreeMonthAgo =
    LOOKUPVALUE (
        Table2[ForcastCore3],
        Table2[RecordDate], EDATE ( Table2[RecordDate], -3 ),
        Table2[Businessline], Table2[Businessline]
    )
RETURN
    IF (
        ISBLANK ( ValueThreeMonthAgo ),
        BLANK (),
        ABS ( Table2[CM_Orders_Actual] - ValueThreeMonthAgo )
            / Table2[CM_Orders_Actual]
    )

Calculation of 2 values different dates_1.jpg

 

Best Regards,
Herbert

Hi Herbert,

 

Still having issues...  I'm sure is something simple but just cannot figure it out...  It does not like ProductLine or RecordDate...

 

 

code.PNG

@mmarshalek

 

It seems that you created a measure with my formula. Please try to create a calculated column instead.

 

Best Regards,
Herbert

What are you putting on the rows?   Just the Date like shown?  If so how are you narrowing down the values to a particular product? Slicing by product number?

 

The measure should be quite straightforward:

 

MAPE =
VAR CM_Ship_Actuals =
    SUM ( Table[CM_Ship_Actuals] )
VAR ForecastCore3MoAgo =
    CALCULATE (
        SUM ( Table[ForecastCore3] ),
        PARALLELPERIOD ( Calendar[Date], -3, MONTH )
    )
RETURN
    DIVIDE ( CM_Ship_Actuals - ForecastCore3MoAgo, CM_Ship_Actuals )

 But it depends on what you have in row/column/slicers or other filters...

dearwatson
Continued Contributor
Continued Contributor

Parallel Period should be your friend here:

 

You need contigeous dates for this to work.. if you dont have running dates use a calendar table 🙂

 

Forecast Current = SUM(Table1[Forecast])

Forecast -3 = CALCULATE([Forecast Current],PARALLELPERIOD(Table1[Date],-3,MONTH))

Capture.PNG

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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