What If analysis - changing a date value in PowerBI

[Robrobrob3]

Hi there,

I have an Excel spreadsheet that contains an IT inventory and determines whether each component (let's say Windows Server OS 2012) is at risk at different time periods (12, 24, 36mo) based on different factors - primarily whether support date will have been surpassed. If it is a risk then 1 is returned, and otherwise 0 is returned.

I have hooked this spreadsheet up to PowerBI for visualisation etc. What I want is to be able to select a component and 'slide' its support date to a different date (simulating purchasing extended support for example). This in turn would alter the risk status at each time period (sliding a date from 2021 to 2024 would mean the risk at the 12 and 24mo intervals should switch from 1 to 0).

What's the best way to go about this? Happy to share any sample data etc. if needs be, thank you!

[amitchandak]

@Robrobrob3 , You need an independent date and what if parameter, based on what I got

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1 * [What if param measure for month]) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Robrobrob3]

Hi there,

Thanks for the response. I played around with this but struggled to get it working.

Just to reframe: I would like to slide a date (let's say by 1 month intervals), each time it passes a defined date, another field is switched from 0 to 1. 

In this example I've 'slid' the date to June 2023; this is more than 12mo in the future so the 'What If' value for Tolerance Breach is set to 0:

Would the solution you shared help achieve this? 

Thank you!

Robin

[v-chenwuz-msft]

Hi @Robrobrob3 ,

I think the method he offers should work. If possible, please share some example data and expect result even if hand-drawn.

Best Regards

Community Support Team _ chenwu zhu