Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello everyone
I have a question about how to convert a date in text format, where the day has 1 or 2 digits and I need to change it to a number so I can use it to calculate today's value (today()) minus this date, to know how long it has been since a ticket was updated.
The final formula will be today's - last update date in number format.
Thank you
Solved! Go to Solution.
Hi @FelippeAzevedo7 ,
To convert the date stored as text into a proper date format in Power BI and calculate the number of days since the last update, you can use either Power Query or DAX. In Power Query, load the data, select the column containing the text dates, and change its data type to Date/Time. If Power Query does not automatically recognize the format, use the "Change Type" option with a locale setting that matches the date format. Once converted, you can create a custom column to calculate the difference from today’s date using the following formula:
DaysSinceUpdate = Duration.Days(DateTime.LocalNow() - [Last update date])
This will return the number of days that have passed since the last update date.
If you prefer using DAX, first ensure the "Last update date" column is in a Date/Time format. If it is still in text format, convert it using:
LastUpdateDateConverted = DATEVALUE([Last update date])
Then, create a measure to compute the difference in days:
DaysSinceUpdate = DATEDIFF([LastUpdateDateConverted], TODAY(), DAY)
If the column contains timestamps, you may need to strip the time portion by using INT([LastUpdateDateConverted]). This approach ensures accurate date comparisons and allows the calculation of the number of days since each ticket was last updated.
Best regards,
Hello DataNinja777
I tested but I found another issue.
I did as you said, but there is a problem with the date, it is inverting the day with the month.
DATEVALUE(rawdata[Last update date]) = Nov 3 2025
But the correct date would be March 11, 2025
Many tks for the feedback. I will test it now.
Have a great day!
Hi @FelippeAzevedo7 ,
To convert the date stored as text into a proper date format in Power BI and calculate the number of days since the last update, you can use either Power Query or DAX. In Power Query, load the data, select the column containing the text dates, and change its data type to Date/Time. If Power Query does not automatically recognize the format, use the "Change Type" option with a locale setting that matches the date format. Once converted, you can create a custom column to calculate the difference from today’s date using the following formula:
DaysSinceUpdate = Duration.Days(DateTime.LocalNow() - [Last update date])
This will return the number of days that have passed since the last update date.
If you prefer using DAX, first ensure the "Last update date" column is in a Date/Time format. If it is still in text format, convert it using:
LastUpdateDateConverted = DATEVALUE([Last update date])
Then, create a measure to compute the difference in days:
DaysSinceUpdate = DATEDIFF([LastUpdateDateConverted], TODAY(), DAY)
If the column contains timestamps, you may need to strip the time portion by using INT([LastUpdateDateConverted]). This approach ensures accurate date comparisons and allows the calculation of the number of days since each ticket was last updated.
Best regards,
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |