Forum Discussion
Solved
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 ...
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
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
Community Champion
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!