Forum Discussion
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 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")
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.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" )
5 Replies
Jay2077 You are mixing DAX and M. In M you would use something like DateTime.LocalNow()
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.This is great thanks how would i go about counting rows of expired and non expired from there then please ?
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" )
