cancel
Showing results 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

Frequent Visitor

## Calculation of 2 values different dates

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

Thanks again!

Employee

@mmarshalek

Please try to create a calcuated column with following formula.

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

Best Regards,
Herbert

Frequent Visitor

Hi Herbert,

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

Employee

@mmarshalek

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

Best Regards,
Herbert

Solution Sage

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

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))

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

Announcements

#### 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 Monthly Update - June 2024

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

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors