Max of date column isn't correct

[Anonymous]

Hi, super basic question I'm sure, but I'm brand new at this, so please bear with me!

I'm trying to create a measure to approximate the date that the dashboard was updated. One of the columns in my data set is a list of dates that should be updated enough to pull the latest date from that column. For some reason, though, every time I use a max function, it pulls 12/31/2019, which definitely isn't in the data set. I added a filter so the date <today(), but it pulls up yesterday's date now, which also isn't in the data set. Just doing a max on the Excel file, I get the correct date, so I'm not sure why PowerBI is finding something different. Could it be because there are blanks in the column?

Here's my measure:  DateUpdated = calculate(max(MasterInspections[123InspectionMostRecent].[Date]),MasterInspections[123InspectionMostRecent].[date]<today()) Thanks in advance!!

[PANDAmonium · Accepted solution]

If you are actually using a report in app.powerbi.com, the new look will include the last updated time. If you are using a dashboard or Desktop though, I'm fairly sure this will work (It something I thought of awhile ago but haven't actually tried it yet)...

1. In query editor, create a new table. It should have one column, one row with a value of "Updated" or something
2. Add a new column with "= Table.AddColumn(Source, "Custom", each DateTime.LocalNow())"

You should end up with something like

Col 1 | Col 2

Updated | 9/4/2019 5:05:05 PM

What should happen is, whenever the dataset is refreshed, it will update that value with the current DateTime. From there, create a card with that value and place it anywhere in your report or dashboard. So no approximations but the actual time the dataset was updated.

[parry2k]

@PANDAmonium great solutions/options but still need to understanding why he i getting dec 31s, 2019 as date when it it is not in the table.

[PANDAmonium]

It's because the .[Date] part switches the reference to dates in general, not the column. So max date is last day in year, 12/31. The measure should just be =max(table[col]). Also explains why the second function returns yesterday. Max date less than today is yesterday.

So your dataset should be fine. Either use =max(table[col]) to approximate date of last update or use the method I explained earlier to pull the current time during an update.

[parry2k]

I would recommend to turn off auto date/time intelligence and that is causing the issue. It just bloat the model by adding hidden date tables for each date type column.

[parry2k]

@Anonymous not sure why max will return Dec 31, 2019 if thsi is not in your table. this doesn't make sense. Do you have relationship with other table? How does your data model looks like?