cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jen8080
Helper I
Helper I

Excel IFS to DAX

Hello-

I have this formula in my current excel report 

=@IFS(Y3="","Missing Complete Date",(Y3-W3)<0,"On Time",(Y3-W3)=0,"On Time",(Y3-W3)>0,"Missed")

 

I need to translate into DAX and keep running into issues.

'Y' = Actual Complete Date

'W'= Target Date

 

Thank you! 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try this calculated column formula

=if(data[actual complete date]=blank(),"Missing Complete Date",if(data[target date]>=data[actual complete date],"On time","Missed"))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Try this calculated column formula

=if(data[actual complete date]=blank(),"Missing Complete Date",if(data[target date]>=data[actual complete date],"On time","Missed"))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Super User
Super User

Not sure if you are looking for a column or measure, but please try this measure expression.  You can adapt for column by removing the aggregation on both - Table[Date1] instead of MIN(Table[Date1])

 

New Measure =
VAR vDateDiff =
    DATEDIFF (
        MIN ( Table[Date1] ),
        MIN ( Table[Date2] ),
        DAY
    )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( vDateDiff )"Missing Complete Date",
        vDateDiff >= 0"On Time",
        vDateDiff < 0"Missed"
    )

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


pranit828
Super User
Super User

=@IFS(Y3="","Missing Complete Date",(Y3-W3)<0,"On Time",(Y3-W3)=0,"On Time",(Y3-W3)>0,"Missed")

Hi @jen8080 

Try

SWITCH(TRUE(),
        Y3="","Missing Complete Date",
       (Y3-W3)<0,"On Time",
       (Y3-W3)=0,"On Time",
       (Y3-W3)>0,"Missed")




PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Hello- I tried this and get the 'DAC Comparison opertations do not support comparing calues of type Date with Values of type text'.

jen8080_0-1608154849366.png

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors