Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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:

 

forecast.PNG

 

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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Super User
Super User

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)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors