Benchmarking - how to ensure that date in filter is always fixed to the minimum possible date?
I have sort of an interesting use case that I haven't really seen here before. In short, we are trying to create a forecast that is based precisely on the trend line shape of a prior year - in our case, 2019 since 2020 is such an outlier. We want to basically say, "well, if we follow the precise trend of any given prior year (e.g. 2019), here is where we would expect to be at later in the month". In short, here is what I need to do:
1) Take the difference between the 2021 line and 2019 line on the first day of comparison. This difference should be a FIXED number throughout the entirety of the visualized period.
2) Subtract that number (for all dates in the period) from the 2019 line so that the new forecast line begins on the same start date and value as the actual 2021 line. Here's an example:
As you can see, the orange dashed line has the same shape as 2019, but is benchmarked against the .91% value on March 1st, 2021.
So far, so good. But I have another requirement; I need to be able to shift the benchmark to any day of the user's choosing within the specified time period (for this visualization it will always be the most recent two months). And this is the part that I have been having a lot of trouble with. I was only able to achieve the above visualization by fixing the date as being equal to DATE(2021,3,1), but when I try using a function like MIN() or FIRSTDATE(), the date used in the function is not the first date in the period here. Here is the measure I am using to benchmark to 3/1/2021 right now in the above screenshot:
Sounds like an excellent scenario for the What-If parameters in Power BI. You can sense the delta range for the parameter from your fact data, and then allow the user to play with values between the min and max differences (which would shift the line up and down as you require)