Date as text and with different format (days between 2 dates)

[FelippeAzevedo7]

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

[FelippeAzevedo7]

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

[FelippeAzevedo7]

Many tks for the feedback. I will test it now.

Have a great day!

[DataNinja777 · Accepted 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,