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/

View solution in original post

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

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
Top Kudoed Authors