If functions with greater than date test?

[Dakotars]

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

[Anonymous]

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")

[Ashish_Mathur]

Hi,

Write this calculated column formula

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

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

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. 

[Ashish_Mathur]

Yes, you can.

[Anonymous]

So Data model can automate tasks in excel! Sweet thanks 

[Anonymous]

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()&>

[Ashish_Mathur]

Hi,

Try this

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

Hope this helps.

[Anonymous]

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

Lisa

[SteveCampbell · Accepted solution]

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 ( 2016, 9, 30 )
        &&  wave_client_details[Wave status] = "1. Surveyed" ,
    "Yes",
    "No"
)

[Dakotars]

Worked like a charm - thanks so much! 

(And definitely moving forward with using &&)