Forum Discussion
Create a custom column with a new date based off another's column date
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?
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.
3 Replies
- Greg_Deckler
Community Champion
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.
- Icey
Community Support
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.
