Forum Discussion

PaulaL's avatar
PaulaL
Frequent Visitor
7 years ago
Solved

increase date column based on conditions

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      PaulaL
      Frequent Visitor
      7 years ago

      AnonymousThank 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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        PaulaL,

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

        Regards,
        Lydia