Forum Discussion

Jay2077's avatar
Jay2077
Icon for Helper III rankHelper III
5 years ago
Solved

IF less than today with dax

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...
Need Help
Tips and Tricks
  • edhans's avatar
    edhans
    5 years ago

    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's avatar
    edhans
    5 years ago

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



Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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