IF less than today with dax

[Jay2077]

So i have a column of dates and i want to add a column which contains a formula saying if that data is before today then "yes" else "no" 

when you do the conditional column and say before you can put a date but not TODAY()  How do i replace 2121, 1, 1 with TODAY()  ?

= Table.AddColumn(#"Renamed Columns", "Custom", each if [#"Expiry"] <= #date(2021, 1, 1) then "Expired" else "Not-Expired")

[edhans · Accepted solution]

Greg is right - to be specific, change your formula to this

Table.AddColumn(#"Renamed Columns", "Custom", each if [#"Expiry"] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Not-Expired")

DateTime.LocalNow() is equivalent to NOW() in Excel.
Wrapping it in DateTime.Date() converts it to just the day - strips the time off of it, so it will do the comparison correctly.

[edhans · Accepted solution]

Greg is right - to be specific, change your formula to this

Table.AddColumn(#"Renamed Columns", "Custom", each if [#"Expiry"] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Not-Expired")

DateTime.LocalNow() is equivalent to NOW() in Excel.
Wrapping it in DateTime.Date() converts it to just the day - strips the time off of it, so it will do the comparison correctly.

[Jay2077]

This is great thanks how would i go about counting rows of expired and non expired from there then please ?

[edhans · Accepted solution]

In Power Query? You could group by the new column and use COUNT as the aggregation, but I'd be inclined to that in DAX, as that is analysis. So

Expired Count =
CALCULATE(
    COUNTROWS( MyTable ),
    MyTable[ExpiredField] = "Expired"
)

[Jay2077]

Thank you

[Greg_Deckler]

@Jay2077 You are mixing DAX and M. In M you would use something like DateTime.LocalNow()