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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.