Creating a Non-Volatile TODAY() function in New Column

[bjpowell93]

Hi all,

I have a dataset that will be refereshed regularly and contains c.450,000 rows of individual products. In each row are a series of pass/fail criteria in different columns. Once all of the criteria say "pass", a status column updates to say "pass", indicating there are no fails in the criteria. 

What I would like, is a new column that states the date on which the status column updated to say "pass". 

I have tried using the TODAY() function, but because it's volatile it doesn't stay fixed because I don't want this newly input date to roll-on, rather be fixed to the date the status changed. 

I'm not sure whether this is best tackled by simply adding a new column and inputting a formula, or whether it should be a conditional column, custom column, or done by invoking a custom function. 

Any solutions would be hugely appreciated!!

Many thanks!

[v-chenwuz-msft · Accepted solution]

Hi @bjpowell93 ,

No, power bi is sent query to data source to get data then calculate. If you refresh, it will refresh the whole data, so the calculation result won't be saved. It means you can not keep the date created by today()

You can try add the date in original data source or maybe you can try python script in power bi query, run python script to save the date then power bi get data from the python script result.

Run Python Scripts in Power BI Desktop - Power BI | Microsoft Docs

Best Regards

Community Support Team _ chenwu zhu

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

[v-chenwuz-msft · Accepted solution]

Hi @bjpowell93 ,

No, power bi is sent query to data source to get data then calculate. If you refresh, it will refresh the whole data, so the calculation result won't be saved. It means you can not keep the date created by today()

You can try add the date in original data source or maybe you can try python script in power bi query, run python script to save the date then power bi get data from the python script result.

Run Python Scripts in Power BI Desktop - Power BI | Microsoft Docs

Best Regards

Community Support Team _ chenwu zhu

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

[bjpowell93]

Hi @v-chenwuz-msft,

Thanks for these ideas - do you know what the Python script would look like?

Using Python isn't something I'vr done before. 

Thanks!

[v-chenwuz-msft · Accepted solution]

Hi @bjpowell93 ,

Fill the date with the formula in the power query.

Add custom column to fill which row has no date.

Then the resulting result is rewritten to the file. I use the .csv file format here.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

[amitchandak]

@bjpowell93 , add in power query

Date.From(DateTime.FixedLocalNow())

https://www.enhansoft.com/how-to-add-the-last-refreshed-date-and-time-to-a-power-bi-report/

[bjpowell93]

Hi @amitchandak,

Unfortunately, when I've refreshed my report today with Date.From(DateTime.FixedLocalNow()), the date has now changed to today's date, not stayed at yesterday's date. 

Do you know why this is? 

[bjpowell93]

@amitchandak 

Would this be correct ("RFM Pass/Fail" is the status column):

if [#"RFM Pass/Fail"] is Pass then Date.From(DateTime.FixedLocalNow()) else null

[bjpowell93]

To clarify, what I'd need is a date the status changed to "pass" upon a daily refresh, input in a new "Date Status Pass" column for every product row. Any that don't have a status of "pass" will be left blank in this new date column.

[bjpowell93]

So, create a new column in power query - is this a custom column this would be input into?

To double-check, the next time the data is refreshed, the newly input date wouldn't change?

How do I integrate this Date.From(DateTime.FixedLocalNow()) function into an IF statement for the status changing to "pass"? 

I can't see "IF" as an option for a custom column.

Thanks!