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.
Hello,
I'm new to powerBI so I think this should be easy.
I want to create a custom column based off another column's date. If the original column is before today I want the custom column to show todays' date
If the date is after today then I want to show the original date.
If this was in excel the formula would be this: =IF(A2<TODAY(),TODAY(),A2)
Is there an easy way to do this?
Solved! Go to Solution.
Hi @Anonymous ,
Power Query Editor is used to connect to one or many data sources, to shape and transform the data to meet your needs. Then you can load that model into Power BI Desktop to create reports.
For Power Query Editor, you can refer to this document: https://docs.microsoft.com/en-us/power-bi/desktop-query-overview.
For Power BI Desktop, you can refer to this document: https://docs.microsoft.com/en-us/power-bi/desktop-what-is-desktop.
Then, let's answer your question.
If you want to create a custom column in Power Query Editor, you can try this:
1. Add Column -> Custom Column.
2. Type the M expression:
= if [Date] < Date.From(DateTime.LocalNow()) then Date.From(DateTime.LocalNow()) else [Date]
3. Then you can get a custom column that meets your requirements.
If you want to create a Calculated Column in Power BI Desktop. Try this:
1. Modeling -> New Column.
2. Type the DAX expression:
Column = IF([Date]<TODAY(),TODAY(),[Date])
For details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
In DAX it is very similar:
Column = IF([Date]<TODAY(),TODAY(),[Date])
Thanks...I'm very new to PowerBI so I think I'm trying to enter the formula in the wrong spot.
I went to Power Query Editor and clicked on Custom Column. I followed the wizard and typed in the synatex like this:
=IF(Loading Date]<TODAY(),TODAY(),[Loading Date])
This then became the below:
= Table.AddColumn(#"Changed Type2", "Custom", each IF([Loading Date]<TODAY(),TODAY(),[Loading Date]))
and I got this error: Expression.Error: The name 'IF" wasn't recognized. Make sure it's spelled correctly.
I must be doing something very simple that isn't right.
Hi @Anonymous ,
Power Query Editor is used to connect to one or many data sources, to shape and transform the data to meet your needs. Then you can load that model into Power BI Desktop to create reports.
For Power Query Editor, you can refer to this document: https://docs.microsoft.com/en-us/power-bi/desktop-query-overview.
For Power BI Desktop, you can refer to this document: https://docs.microsoft.com/en-us/power-bi/desktop-what-is-desktop.
Then, let's answer your question.
If you want to create a custom column in Power Query Editor, you can try this:
1. Add Column -> Custom Column.
2. Type the M expression:
= if [Date] < Date.From(DateTime.LocalNow()) then Date.From(DateTime.LocalNow()) else [Date]
3. Then you can get a custom column that meets your requirements.
If you want to create a Calculated Column in Power BI Desktop. Try this:
1. Modeling -> New Column.
2. Type the DAX expression:
Column = IF([Date]<TODAY(),TODAY(),[Date])
For details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |