increase date column based on conditions

[PaulaL]

Hi All, I am having some issues with how to approach my query so any help would be greatly appreciated.

I have a date column that I need to increase based on two other columns values.

e.g. Date Reported column - 17/12/2018

If my Impact Column = "Urgent" and my Department = "Stores" I would need to increase my Date Reported Column to 18/12/2018

However if my Impact Column = "Standard" and my Department = "Floor" I would need to increase my Date Reported Column to 20/12/208

I would ideally like to not touch the original Date Reported Column but move this new value to another column.

So Far I have created a custom column and this is my code however it doesnt work.

AmendedDateReported = if(And(SurveyCorrectiveAction[Impact] = "Urgent", SurveyCorrectiveAction[LookUp] = "Stores"), Date.AddDays([DateReported],1),Blank ())

Thanks

Paula

[v-yuezhe-msft · Accepted solution]

@PaulaL,

If you create  a custom column in Power BI Desktop query editor, please use formula below.

if [Impact] = "Urgent" and [LookUp] = "Stores" then Date.AddDays([DateReported],1) else if
[Impact] = "Standard" and [LookUp] = "Floor" then Date.AddDays([DateReported],3) else null

If you create a calcualted column, please using DAX below.

Column = if (and(SurveyCorrectiveAction[Impact] = "Urgent", SurveyCorrectiveAction[LookUp] = "Stores") ,SurveyCorrectiveAction[DateReported]+1 ,if(and(SurveyCorrectiveAction[Impact] = "Standard" , SurveyCorrectiveAction[LookUp] = "Floor" ), SurveyCorrectiveAction[DateReported]+3,BLANK()))

Regards,

Lydia

[PaulaL]

@v-yuezhe-msftThank you very much for this, will it also work if I need to add multiple other impacts and departments? if I expand the else if?

[v-yuezhe-msft]

@PaulaL,

It should work. If you get any issues using above formula, you can post back with sample data and expected result.

Regards,
Lydia