Dynamic Calculated Columns using What If Parameter

[wrwillits]

Can you use What If Parameters to dynamically recalculate a calculated column? I need to allow the user to change dates that are being used to forecast out component values. I currently have a fixed future date in each row of data for each component that I am using to forecast the future value for that componet (value deterorates over time), but the customer wants to make changes to that date on the dashboard and then immediately see the revised forecast based on the slected future date. I tried using a What If Parameter to allow the user to add days to the date (Fixed Date + Parameter days = New Future Date) but it won't recalculate the new date. I also tried a Refresh thinking that the system would recalculate the column vales if I refreshed the data but that didn't work either. Can Parameters only be used to filter a table or with Measures? 

[parry2k · Accepted solution]

It can be only used in measures not in columns. You can add a measure for you date calculation and use that.

[parry2k · Accepted solution]

It can be only used in measures not in columns. You can add a measure for you date calculation and use that.

[Hawk_Wang]

---

@parry2k wrote:

It can be only used in measures not in columns. You can add a measure for you date calculation and use that.

---

Hi parry2k, 
 I encounter the similar issue, and also want to use the IF function in new column.
Could you tell me why it is not support in columns?
Thank you for your reply.

[wrwillits]

Thanks for hte response.

Unfortunately the remaining useful life for each row of data (each component) needs to be recalculated based on the user selected futrure date so I don't think I can make it work by only adding a measure for the date (I actually tried that but without being able to trigger a recalculation of the row data, it does nothing). 

[wrwillits]

We are working with components that are installed on commercial aircraft that are leased into operators. What we need to do is to calculate the amount of remaining value each componet has when it is returned off lease. The "dynamic" component comes into play because the Lessor wants to be able to change the lease return date and see what impact the new date has on the remaining life of the components. 

Here's a video of an Excel version of what I'm trying to do.

Dynamic row re-calculation

[parry2k]

Sure, it is achievable, if you share the excel file with formulas you have, will work out something, 

[wrwillits]

 Happy to share the Excel example but not sure how to attach a file to the message (no file attachment capability that I can see).

[parry2k]

@wrwillits you cannot attach, share it thru google drive or other preferred way.

[wrwillits]

Here you go: https://jetstarpartners.box.com/s/g8xurt2mohdl8ifrw4vtk9yu09h2ojoo

[parry2k · Accepted solution]

here is the link to pbix

Feel free to change calcs as you see fit. all the measure starts with x are the calculated measures.

All there is paramter for path of excel file which you can change to point to you excel file.

[Anonymous]

@parry2k 

Unable to access to the pbix file, can you reshare it

[Maria_Sanse]

Hi @parry2k 

Can you please share again the PBI link? I cannot open it, and I am facing a similar issue than this post.

Thanks!

[swatiupadhyaya]

this is very elaborate and helpful.

Although i am trying to do the same with a a range of decimal values instead of date i.e.

a what if paramter created threshold -  0 to 1 with increment 0.1.

my calculated field

x threshold_test2 = if(HASONEVALUE(thresh_test_2[threshold]),VALUES(thresh_test_2[threshold]),0.5) always returns the default 0.5 even if you select any other data from splicer.

Can you please help.

Thanks

[swatiupadhyaya]

this is very elaborate and helpful.

Although i am trying to do the same with a a range of decimal values instead of date i.e.

a what if paramter created threshold -  0 to 1 with increment 0.1.

my calculated field

x threshold_test2 = if(HASONEVALUE(thresh_test_2[threshold]),VALUES(thresh_test_2[threshold]),0.5) always returns the default 0.5 even if you select any other data from splicer.

Can you please help.

Thanks

[wrwillits]

Great, thanks!