cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Max of date column isn't correct

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!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver III
Resolver III

Re: Max of date column isn't correct

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.

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Max of date column isn't correct

@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?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Resolver III
Resolver III

Re: Max of date column isn't correct

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.

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Max of date column isn't correct

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Resolver III
Resolver III

Re: Max of date column isn't correct

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.

Highlighted
Super User IV
Super User IV

Re: Max of date column isn't correct

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.





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors