How to replace a value with todays date ?

[DynamicsHS]

Hi, 

I have a list of dates and some of them are presenting as '31/12/2154'. How can I change those rows listed as '31/12/2154' to todays date?

So that every time it refreshes, it shows todays new date. 

Thank you,

Henry

[v-rongtiep-msft]

Hi @DynamicsHS ,

Please have a try.

Create a measure.

measure =
IF ( MAX ( table[date] ) = "31/12/2154", TODAY (), table[date] )

Or add a custom column.

if [date] = "31/12/2154" then DateTime.LocalNow() else [date]

If I have misunderstood your meaing, please provide more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DynamicsHS]

Hi @v-rongtiep-msft , 

I am getting this error:

the date is formatted as a date and not text?

[v-rongtiep-msft · Accepted solution]

Hi @DynamicsHS ,

Change the measure.

measure = 
IF ( MAX ( 'Table'[date] ) = DATE(2154,12,31), TODAY (), MAX('Table'[date]) )

If I have misunderstood your meaing, please provide more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DynamicsHS]

Hi @v-rongtiep-msft , 

This has worked but values (dates) that are not 31/12/2154  are coming up as todays date? instead of showing their orginal date? e.g. 27/10/2021 is showing as todays date and not just 27/10/2021

[DynamicsHS]

just needed to change the dates around to 31, 12,2154 and its worked. Thank you

[amitchandak]

@DynamicsHS , refer how can replace date value in power query

https://community.powerbi.com/t5/Power-Query/Replace-value-based-on-date-column/td-p/960200