DAX Function to Show Date Modified of a Column Value

[cczaniec]

Hi PowerBI Community,

I'm pulling a Direct Query into PowerBI of specific records. I have a column that shows the "STATUS" of each record (Values can be "Proposed", "Approved", "Closed"). I would like to create a column that shows the Approval Date (i.e. when the STATUS value changed from "Proposed" to "Approved"). 

I created the following IF statement Approval Date = IF(Records[STATUS]="Approved",TODAY()) , but the date will keep updating each day. I need something that will only provide the Approval Date once it was approved.

Let me know if this is possible - thanks! 

[FreemanZ]

hi @cczaniec 

try like:

Approval Date=

IF(Records[STATUS]="Approved", Records[date])

[cczaniec]

Thanks for the reply. This formula would not work as I do not have a column in the data table named "date". That's what I'm trying to produce 🙂

[FreemanZ]

hi @cczaniec 

do you have other date relevant columns?

[cczaniec]

I have other Date columns but they represent other dates (e.g Date of Event). I don't have anything from my Direct Query that represents the date the event was approved.

[Anonymous · Accepted solution]

Hi @cczaniec ,

According to your statement, you want to create a calculated column by Dax to fix the changing date when event status changed from "Proposed" to "Approved" instead of dynamic today after everytime refresh. Whether Dax or Power Query are mashup tools, not data warehouse.

As far as I know, the calculation in Power BI is based on columns. In your table, there should be at least one column to determind the date to tell Power BI how to fix the date. Today() is a function which will catch the every date dynamiclly. In other words, any dynamic calculations in Power BI are based on fact tables. So it is no possible to create a fix date column without any enter data with the date information.

Options might be:

1) Create a 'today's date' column in your query, then use incremental refresh to only refresh a very narrow partition each day, so the dates in the historical partition are retained:

Incremental refresh and real-time data for datasets

2) Have a Slowly Changing Dimension (SCD) table built on the source, such as an SQL stored procedure run daily on the source, with the results written to a table each time.

Best Regards,
Rico Zhou

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