Forum Discussion
Latest & Earliest Date/Time Function Not Reporting Correctly
I have data that shows the date and time for file reciept; I am trying to create cards that show what the EARLIEST file receipt time was and what the LATEST file receipt time was in the given set of data. I have researched for quite some time and have not found anything to point me to the cause of why this is not reporting correctly... please help!
Here is a sample of the data I am using:
The earliest time a file was received should be reporting as from 1/24/2023 at 12:01:59 AM and the latest time a file was received should be reporting as 2/2/2023 at 12:16:00 PM; however, when I create a card visual and tell it to report earliest and latest from the Last File Receipt Time field, it is reporting this:
I have even attempted to create new measures with MIN and MAX functions and it reports the same way.
DataIsMyMN Oh, you want the items with the min or max time regardless of date. If that is the case see attached PBIX below signature.
Earliest = VAR __Table = ADDCOLUMNS( 'Table', "Time", [Last File Receipt Time] - TRUNC([Last File Receipt Time]) ) VAR __MinTime = MINX(__Table, [Time]) VAR __Result = MINX(FILTER(__Table, [Time] = __MinTime),[Last File Receipt Time]) RETURN __ResultLatest = VAR __Table = ADDCOLUMNS( 'Table', "Time", [Last File Receipt Time] - TRUNC([Last File Receipt Time]) ) VAR __MaxTime = MAXX(__Table, [Time]) VAR __Result = MINX(FILTER(__Table, [Time] = __MaxTime),[Last File Receipt Time]) RETURN __Result
4 Replies
- Greg_Deckler
Community Champion
DataIsMyMN Is that column a Date/Time column?
Greg_Deckler yes, it is a Date/Time column. I've tried changing the display format to only show the time, but that doesn't fix it.
- Greg_Deckler
DataIsMyMN Oh, you want the items with the min or max time regardless of date. If that is the case see attached PBIX below signature.
Earliest = VAR __Table = ADDCOLUMNS( 'Table', "Time", [Last File Receipt Time] - TRUNC([Last File Receipt Time]) ) VAR __MinTime = MINX(__Table, [Time]) VAR __Result = MINX(FILTER(__Table, [Time] = __MinTime),[Last File Receipt Time]) RETURN __ResultLatest = VAR __Table = ADDCOLUMNS( 'Table', "Time", [Last File Receipt Time] - TRUNC([Last File Receipt Time]) ) VAR __MaxTime = MAXX(__Table, [Time]) VAR __Result = MINX(FILTER(__Table, [Time] = __MaxTime),[Last File Receipt Time]) RETURN __ResultGreg_Deckler, you are a LIFESAVER, thank you! This worked beautifully!
