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

Post Patron

## How to do a calculation between 3 unrelated tables (date specific)

I have 3 tables:

1. Stock Forecast which includes the field 'Total Orders' (which is a measure)

2. Sales Forecast which includes the field 'Sales Forecast at COGS' (which is also a measure)

3. Stock Value which includes the field Cost Amount (Actual)

They are all unrelated which each other but all do have a relation with the calendar table.

Here is some pseudocode of how I would like to have my calculation:

Stock Level = IF month = CURRENT MONTH THEN SUM('Total Orders' of past 3 months) + SUM('Cost Amount (Actual)') + SUM('Sales Forecast at COGS' of the current month)

ELSE SUM('Total Orders') + SUM('Sales FC at COGS') + SUM('Stock Level' of previous month)

2 REPLIES 2
Super User

@Niels_T , Try with help from time intelligence

if( eomonth(max('Date'[Date]),0) = eomonth(today(),0) , CALCULATE(Sum(Orders[Total Orders]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH)) + SUM('Cost Amount (Actual)'[Amount]) + CALCULATE(SUM('Sales Forecast'[COGS]),DATESMTD(dateadd('Date'[Date],-1*month(Today()) +1 ,MONTH))) ,
Sum(Orders[Total Orders]) + SUM('Cost Amount (Actual)'[Amount]) + CALCULATE(SUM('Sales Forecast'[COGS]),DATESMTD(dateadd('Date'[Date],-1 ,MONTH)))
)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

Post Patron

Hello Amit,

Thank you for the function.

There is one thing I would like to ask.

I saw you wrote MAX('Date'[Date ]),-3,MONTH), however, my 'Date'[Date] extends 5 years into the future. So I should take off 5 years right?

If yes how can I do this?

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 - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors