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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.