Benchmarking - how to ensure that date in filter is always fixed to the minimum possible date?

[Anonymous]

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:

Rate Benchmark = CALCULATE([C Rate 2PY (Forecast)],

                                                   dimDate[Date] = DATE(2021,3,1))

                                                - CALCULATE([dq_ar_switch] / [$ CY AR],

                                                   dimDate[Date] = DATE(2021,3,1))

What is most important to me is if it is possible to fix a date in a measure to be the minimum possible date in a period without explicating the date itself, as I have done in the above measure.

Thank you

[Anonymous · Accepted solution]

Hi, I was able to solve this and achieve my objective with the following measure:

Rate Benchmark =

var _min = minx(ALLSELECTED(dimDate), dimDate[Date])

return CALCULATE([DQ Rate 2PY (Forecast)], dimDate[Date] = _min) - CALCULATE([dq_ar_switch] / [$ CY AR], dimDate[Date] = _min)

[lbendlin]

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)