Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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")
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIn 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"
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGreg 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis 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"
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you
@Jay2077 You are mixing DAX and M. In M you would use something like DateTime.LocalNow()
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.