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

[Niels_T]

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)

[amitchandak]

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

[Niels_T]

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?