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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors