Forum Discussion
Dynamic Calculation Based on Date
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 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
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.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?
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!