cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors