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
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
Responsive Resident
Responsive Resident

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

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

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.