Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I'm trying to formulate in power query the following issue:
From a column called "Difference" and the day I am, I want to add a column in which it tells me If I "executed" the activity, "not executed", is "planned" is "extra executed" or simply there's no plan "Unplanned".
So the idea is:
IF [Difference]=0 and not [YTDfinal]=0 then "Executed" If [Difference]<0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Extra executed" IF [Difference]>0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Not executed" IF [Difference]>0 and DateTime.Date(Date.Time.LocalNow())<[fechainicio] then "Planned" IF [YTGfinal]>0 then "Planned" else "Unplanned".
I made the same formula in power bi desktop and it was ok:
Ejecutadoname = IF(AND(Cruce[Diferencia]=0,Cruce[YTD]<>0),"Executed",IF(AND(Cruce[Diferencia]<0,TODAY()>Cruce[Date]),"Extra executed",IF(AND(Cruce[Diferencia]>0,TODAY()>Cruce[Date]),"Not executed",IF(AND(Cruce[Diferencia]>0,TODAY()<=Cruce[Date]),"Planned",IF(Cruce[YTG]>0,"Planned","Unplanned")))))
But now that I want to rewrite the formula in power query it doesn't work
Solved! Go to Solution.
Hi @carogomez
Try this:
= if [Difference]=0 and [YTDfinal]<>0 then "Executed" else if [Difference]<0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Extra executed" else if [Difference]>0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Not executed" else if [Difference]>0 and DateTime.Date(Date.Time.LocalNow())<[fechainicio] then "Planned" else if [YTGfinal]>0 then "Planned" else "Unplanned"
Not that M is case sensitive so if ... then... else has to be lower-case.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @carogomez
Try this:
= if [Difference]=0 and [YTDfinal]<>0 then "Executed" else if [Difference]<0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Extra executed" else if [Difference]>0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Not executed" else if [Difference]>0 and DateTime.Date(Date.Time.LocalNow())<[fechainicio] then "Planned" else if [YTGfinal]>0 then "Planned" else "Unplanned"
Not that M is case sensitive so if ... then... else has to be lower-case.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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 |
---|---|
112 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |