Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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,
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
@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?
@PaulaL,
It should work. If you get any issues using above formula, you can post back with sample data and expected result.
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |