Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
PANDAmonium
Resolver III
Resolver III

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
PANDAmonium
Resolver III
Resolver III

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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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.

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.


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.