Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
dsabsi
Advocate I
Advocate I

IF formula with date and today function

Hello everyone, 

 

I'm struggeling with an IF formula in Power BI and I would appreciate your help. 

DateDiff = IF(today()> value(("8-11-2017"));1;if(today()>value("6-12-2017");1;if(today()>value("3-1-2018");1;if(today()>value("7-2-2018");1;if(today()>value("7-3-2018");1;if(today()>value("4-4-2018");1;if(today()>value("9-5-2018");1;0)))))))

 

With this formula I want to have a 0 if the dates that I've put in are not later than today's date (17-10-2017). 

So for example for the first if formula (IF(today()>value ("8-11-2017");1;0) , I expect to get a 0 since that date is in the future. 

Actually all the dates are in the future, so the output should be 0 but it's not working for me. 

 

 

Your help is appreciated!!

Cheers, 


DSabsi

1 ACCEPTED SOLUTION
erik_tarnvik
Solution Specialist
Solution Specialist

Try using DATE(2017,8,11) where you now have value(("8-11-2017")). 

View solution in original post

4 REPLIES 4
alena2k
Resolver IV
Resolver IV

Switch Day and Month and you'll get result you expect; I agree that DATE(Year, Month, Day) is better since DAX will give you a hint

erik_tarnvik
Solution Specialist
Solution Specialist

Try using DATE(2017,8,11) where you now have value(("8-11-2017")). 

@erik_tarnvik@alena2k thank you!!

this works perfectly, however I want the formula to show 0 and not 1 because of the fact that today is not later then those dates but this is not working and i tried both < and >  but it still doesn't show the 0 . 

Does anyone know what I'm doing wrong? 

 

DateDiff = IF (date(2017;8;11)>today();1;

                  if(date(2017;12;6)>today();1;

                  if(date(2018;1;3)>today();1;

                  if(date(2018;2;7)>today();1;

                  if(date(2018;3;7)>today();1;

                  if(date(2018;4;4)>today();1;

                  if(date(2018;5;9)>today();1;0)))))))

 

Cheers, 

DSabsi

 

 

 

date(2017;8;11) is before today and date(2017;12;6) is after so you will get a 1 as a result from this formula.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.