topic Re: Creating a Non-Volatile TODAY() function in New Column in DAX Commands and Tips

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

bjpowell93 — Thu, 10 Mar 2022 10:35:53 GMT

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!

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

amitchandak — Thu, 10 Mar 2022 11:17:15 GMT

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/

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

bjpowell93 — Thu, 10 Mar 2022 11:38:22 GMT

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!

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

bjpowell93 — Thu, 10 Mar 2022 11:41:49 GMT

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.

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

bjpowell93 — Thu, 10 Mar 2022 13:28:09 GMT

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

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

bjpowell93 — Fri, 11 Mar 2022 08:55:34 GMT

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?

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

v-chenwuz-msft — Tue, 15 Mar 2022 05:46:07 GMT

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.

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

bjpowell93 — Tue, 15 Mar 2022 08:16:28 GMT

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!

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

v-chenwuz-msft — Wed, 16 Mar 2022 03:14:11 GMT

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