Forum Discussion
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...
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" )
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 ?