Dynamic Calculation Based on Date

[Anonymous]

Hi Everyone, 
I am writing to ask question to the following problem: I am attempting to calculate deltas based on varying time intervals that are picked by the user with a slicer built on my M Date table . 
Data Tables:

1) Forecasted $ (contains data from 11/23/21 - 12/31/21)

2) Actual $ (contains data from 1/1/21 - 11/22/21)
3) Budget $ (contains data from 1/1/21 - 12/31/21)
4) M Date Table (M table showing all dates for 2019 - 2021). All tables are connected to M Date table through a date column. 
Calculations:
I need to calculate deltas based on 3 different time criterias:

1)Historical - example: (1/1/21 to 10/31/21) -> SUM(Actual $ [1/1/21,10/31/21] ) - SUM(Budget $ [1/1/21,10/31/21])
2)Historical + Future - example: (1/1/21 to 11/31/21) ->
(SUM(Actual $ [1/1/21,11/22/21]) + SUM(Forecast $ [11/23/21,11/31/21])) - SUM(Budget $ [1/1/21,11/31/21]) 
3) Future - example: (12/1/21 - 12/31/21) -> SUM(Forecasted $ [12/1/21,12/31/21]) - SUM(Budget $ [12/1/21, 12/31/21]) 
I can generate the proper results but when I choose different time intervals the measure breaks. On another note, the chart will show all dates from 2019-2021 even though only 2021 dates are selected and the filter shows that only 2021 dates are being used although 2019/2020 appear visually. 

The DAX Formula I used:

Calculation =

// Variables
VAR Cost_MinDate = MIN(Actual$ [Date])
VAR Cost_MaxDate = MAX(Actual$ [Date])
VAR Date_MinSlicer = MIN(MDate[Date])
VAR Date_MaxSlicer = MAX(MDate[Date])
VAR ACT_DATE_PLUS_ONE = Cost_MaxDate + 1

// Variable Calculations
VAR Variable1 =
(CALCULATE(SUM(Actual$[Dollars]),DATESBETWEEN(s_Date[Date],Date_MinSlicer,Cost_MaxDate))
+ CALCULATE(SUM(Forecast$[Dollars]),DATESBETWEEN(s_Date[Date],Cost_Date_Plus_One,Date_MaxSlicer)))

VAR Variable2 = CALCULATE(SUM(Actual$[Dollars]),DATESBETWEEN(s_Date[Date],Date_MinSlicer,Cost_MaxDate))

VAR Variable3 = CALCULATE(SUM(Forecast$[Dollars]),DATESBETWEEN(s_Date[Date],Date_MinSlicer,Date_MaxSlicer))

VAR Variable4 = CALCULATE(SUM(Budget$[Dollars),DATESBETWEEN(s_Date[Date],Date_MinSlicer,Date_MaxSlicer))
// Calculation
RETURN
SWITCH(

TRUE(),

AND(Cost_MinDate >= Date_MinSlicer, Date_MaxSlicer <= Cost_MaxDate),
(Variable2 - Variable4),
AND(Cost_MinDate >= Date_MinSlicer,Cost_MaxDate < Date_MaxSlicer),
(Variable1 - Variable4),
Cost_MaxDate < Date_MinSlicer,
(Variable3 - Variable4)
)

Thank you in advance for your help and time. This problem has been perplexing me for about 2 weeks now. 

[AllisonKennedy]

@Anonymous  what does your relationship model view look like? You may need some unrelated Date tables for each slicer the user can select.

https://excelwithallison.blogspot.com/2021/09/power-bi-forecasting-with-irregular.html 

[Anonymous]

@AllisonKennedy 
Wouldn't there need to be one data table that is connected to each tables date columns (1-many relationship or 1-1) in order to filter properly? My slicer is built using the s_Date table which is controllable by picking a [startdate, enddate]. 
On another note, 
I actually was able to fix the issue by changing the DATESBETWEEN function to reference each individual tables date column instead of "s_Date[Date]" as the date column reference. 
I altered variables within the script to now be: 

VAR Variable1 =
(CALCULATE(SUM(Actual$[Dollars]),DATESBETWEEN(Actual$[Date] ,Date_MinSlicer,Cost_MaxDate))
+ CALCULATE(SUM(Forecast$[Dollars]),DATESBETWEEN(Forecast$[Date],Cost_Date_Plus_One,Date_MaxSlicer)))

VAR Variable2 = CALCULATE(SUM(Actual$[Dollars]),DATESBETWEEN(Actual$[Date],Date_MinSlicer,Cost_MaxDate))

VAR Variable3 = CALCULATE(SUM(Forecast$[Dollars]),DATESBETWEEN(Forecast$[Date],Date_MinSlicer,Date_MaxSlicer))

VAR Variable4 = CALCULATE(SUM(Budget$[Dollars),DATESBETWEEN(Budget$[Date],Date_MinSlicer,Date_MaxSlicer))

No other parts were altered. The deltas evaluate properly (tested under various conditions) and data is no longer being pulled from 2019/2020 on the chart. I believe there must have been a problem using "s_Date[Date]" because it was returning the full date column range of 2019-2021. Let me know your input if you have time, I have often found the calculations using varying timeframe criteria/future dates are difficult to debug. 

[AllisonKennedy · Accepted solution]

@Anonymous  DAX can do all the work that relationships do, so if you want to pick up all data between dates, you don't actually need any relationship. Seems really counter-intuitive, but it works.

Other option is to keep the relationship between Date and Fact table, but then need to use the ALL or similar function  (such as DATESBETWEEN ) to ensure that the relationship doesn't restrict you to only seeing the date that is selected. As you have pointed out, it all comes down to using the right date in the right place in the DAX formulas.

Feel free to mark your most recent post as a solution, as it looks like you've solved this now?

[Anonymous]

Thank you for the thourough explanation, I am going to try the way you suggested so that I can fully understand both options.
Marking your explanation as the answer seeing that it provides a broader level of insight which would be helpful for any other users who have a similar question vs my problem specific answer! Thanks again!