Add number of days to date based on user input

[mvcsarma]

I have spent the past two days breaking my head, reading through the articles with no solution. Turning to the community for help.

Scenario: I have two large tables - I am appending one with another. I'm using multiple date columns (ex: date1, date2, date3) to arrive at a single/final date column: Final_Date. # of units gets aggregated by Final_Date. I need to offer a "What-If" scenario to the user such that they are able to adjust the number of days to see the changes to the aggregated # of units. For instance, moving the slider between 5 and 90, will adjust the per-row Final_Date by the SELECTEDVALUE of the slider. 

What I did: 

What-If Table:

Column_User Input Series= GENERATESERIES(0, 90, 5)

Measure_User Input = SELECTEDVALUE(Column_User Input Series, 90)

Main Table:

1. Final_Date = IF(YEAR(Date1)>2000,[Date1]+Measure_User Input,IF(YEAR(Date2)>2000,[Date2]+Measure_User Input,IF(YEAR(Date3)>2000,Date3+Measure_User Input,Date1+Measure_User Input))) - did not work

I have tried a couple more options which do not work either

I think the problem is that a measure can't be added to transform a column. But, I have tried to create a new column using the measure SELECTEDVALUE('What-If Table'[Column_User Input Series], 90) but this column does not register the "selection" at all. 

If someone has encountered a similar scenario OR, could help with a hint/recommendation/solution, I'd greatly appreciate it.

thank you

[v-yulgu-msft · Accepted solution]

Hi @mvcsarma ,

You want to generate a Final date column whose value is changed according to user's selection, right? However, it is not supported to change column values dynamically. As  calculated column is static once it has been initilized.

Regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @mvcsarma ,

You want to generate a Final date column whose value is changed according to user's selection, right? However, it is not supported to change column values dynamically. As  calculated column is static once it has been initilized.

Regards,

Yuliana Gu

[mvcsarma · Accepted solution]

Yuliana - thank you for your note. 

I saw that this feature needs to be implemented to get my scenario to work.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19789756-dynamic-calculated-colum...

Thanks again!

[mvcsarma]

Adding other options for Main Table (that I tried)

2. Final_Date = IF(YEAR(Date1)>2000,DATEADD([Date1],Measure_User Input,DAY),IF(YEAR(Date2)>2000,DATEADD([Date2],Measure_User Input,DAY),IF(YEAR(Date3)>2000,(Date3,Measure_User Input,DAY),DATEADD(Date1,Measure_User Input,DAY)))) - did not work 3. Final_Date = IF(YEAR(Date1)>2000,[Date1]+SELECTEDVALUE('What-If Table'[Column_User Input Series], 90),IF(YEAR(Date2)>2000,[Date2]+SELECTEDVALUE('What-If Table'[Column_User Input Series], 90),IF(YEAR(Date3)>2000,Date3+SELECTEDVALUE('What-If Table'[Column_User Input Series], 90),Date1+SELECTEDVALUE('What-If Table'[Column_User Input Series], 90)))) - did not work