Date of Data - Last Refresh

[Peekay_Num]

I use DATE OF LAST REFRESH displayed on my App.  The app is set to Auto Refresh every day.  However the data is refreshed weekly.  So the date of the last data may be Feb 25th, the date of the last refresh is going to show March 1st.  That's not what my users want.  They want to know the last ETL date.  What would be the way to capture that?

[Peekay_Num]

It's Excel and I'm thinking that's the only way to go.  Also seems that value will be manually maintained?

[cbschley]

I suggest adding a new column of data to your source data that displays the last time your dataset was refresh (not when the report was refreshed). So if your data has data up through 2/25, then add a column that references that date. Make it a separate column so that the entire column says 2/25.

[Peekay_Num]

This was the thought I initially had.  How does that date populate?  Using a TODAY() function?

[cbschley]

No I wouldn't recommend using a TODAY() function. Since that would update to today's date everytime you refresh your report, which you mentioned happens daily. 

Instead, find an existing date column in your dataset that you think represents how fresh your dataset is, and use a MAXA() function based on that column. That will give you the largest date based on that column. Let's say today is 3/1/2023, but you have a sales_date column where the most recent date is 2/25/2023. You could do something like this:

max_sales_date = MAXA(sales_date)

documentation on this function: https://learn.microsoft.com/en-us/dax/maxa-function-dax

[Peekay_Num]

Unfortunately there are no other date fields.  Only categories and currency

[DmitryKo]

If your source provides no way to guess it's timestamp, then your only option is to use timestamp of the ETL operation. However, this is not what you want.

Therefore you need to add a timestamp field to your data source and use it in your report.

[cbschley]

What is your data source? Is it coming from a Database, or is it a local Excel/CSV file?

If its a Database, you'll want to work with your developers to have some sort of date field that you can work with. Having some sort of date field in a proper fact table is fairly standard practice. 

If it is an Excel/CSV doc, you can that column of data yourself in the file, and then refresh your datasource in Power BI.