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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dakotars
Frequent Visitor

If functions with greater than date test?

Hi there,

 

I'm trying to set up the following if function:

Surveyed in 2017? = IF(AND([Survey timing].[Date]>9/30/2016,(wave_client_details[Wave status]="1. Surveyed")),"Yes","No")

 

However, it looks like the greater than date portion isn't working since I'm seeing "1. Surveyed" results for rows where the date is 2014 or 2015.

 

Any ideas on how I can get the date portion to work correctly?

 

Thanks so much!

Dakota

 

1 ACCEPTED SOLUTION
SteveCampbell
Memorable Member
Memorable Member

Try using the date function:

 


Surveyed in 2017? = IF(AND([Survey timing].[Date]>9/30/2016,(wave_client_details[Wave status]="1. Surveyed")),"Yes","No")

 

Personally, I would use && instead of the and function. It's easier to read and can add multiple ANDs.

 

Surveyed in 2017? = 

IF (
    [Survey timing] > DATE ( 2016930 )
        &&  wave_client_details[Wave status] = "1. Surveyed" ,
    "Yes",
    "No"
)



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



View solution in original post

12 REPLIES 12
Anonymous
Not applicable

  1. So I am trying to compare a collumns  with DateTime.FixedLocalNow() and subtract 90 days from that and say if it's true or false.  This does not work. How do I write it so it compares the times in the column with todays date-90 days. 

=Table.AddColumn(#"Removed Columns", "90day>=", each if [LastDateIn] >= (DateTime.FixedLocalNow()-90) then "true" else "false")

 

in excel it works

=If (c1<=TODAY()-90,"Yes","No")

Hi,

Write this calculated column formula

=if(Data[LastDateIn]<=today()-90,"Yes","No")


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

Is that trying to convert the Column to DateTime? The excel function I used worked. But in power Query does not.

My solution will work in the Data Model (not in the Query Editor).  Mine is a calculated column formula (to be used in DAX) not an M language formula.


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

Can I use that for automation? I'm just trying to automate excel so I don't have to keep doing the same thing over again. 

Yes, you can.


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

So Data model can automate tasks in excel! Sweet thanks 

Anonymous
Not applicable

I have a similar problem.  I have a table which I would like to show a sum of Today and Greater Than total sum.

 

ex: Todays100 Pull Rate 85% = CALCULATE(SUM(PullRateTable[100M Needed]) *.85)

FILTER('DateTable',
'DateTable'[Date] = TODAY()&>

 

 

Hi,

 

Try this

 

=CALCULATE(SUM(PullRateTable[100M Needed])*.85,FILTER('DateTable','DateTable'[Date] >= TODAY()))

 

Hope this helps.


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

Thanks Ashish!  I'll see if I get the correct total for tomorrow.

Lisa

SteveCampbell
Memorable Member
Memorable Member

Try using the date function:

 


Surveyed in 2017? = IF(AND([Survey timing].[Date]>9/30/2016,(wave_client_details[Wave status]="1. Surveyed")),"Yes","No")

 

Personally, I would use && instead of the and function. It's easier to read and can add multiple ANDs.

 

Surveyed in 2017? = 

IF (
    [Survey timing] > DATE ( 2016930 )
        &&  wave_client_details[Wave status] = "1. Surveyed" ,
    "Yes",
    "No"
)



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Worked like a charm - thanks so much! 

 

(And definitely moving forward with using &&)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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