DAX - Getting Earliest Date Time

[Anonymous_226]

Hi,

I am currently looking at a table of data like the below:

Number   Value       Date/Time

001                          12/13/2023 8:29:05 AM

001          Group 1   12/13/2023 8:30:06 AM

001          Group 2   12/14/2023 12:36:42 PM

001          Group 3   12/18/2023  5:04:36 AM

001          Group 4   12/18/2024  4:51:38 AM

001          Group 1    1/1/2024  3:50:26 AM

001          Group 2    1/1/2024  6:50:26 AM

I have these columns in a table visual along with a measure and in the measure I am trying to return the earliest start time for "group 1". I also have a slicer that i use to get the dates in my variables from(this slicer is in an unrelated table from the table my data is in.

So far this is my DAX:

Measure 1 =

VAR SelectedMonthStartDate = MINX(SomeTable, SomeTable[Created])
VAR Sundayfollowingthe1st = IF(FirstDayOfMonth + MOD(8 - WEEKDAY(FirstDayOfMonth), 7) = SelectedMonthStartDate, (FirstDayOfMonth + MOD(8 - WEEKDAY(FirstDayOfMonth), 7) + 7), (FirstDayOfMonth + MOD(8 - WEEKDAY(FirstDayOfMonth), 7))+1)

VAR AbsoluteEarliestStartPerTicket =
SUMMARIZE(
SomeOtherTable,
SomeOtherTable[Number],
"EarliestStart", MIN(SomeOtherTable[Date/Time])
)

RETURN COUNTROWS(AbsoluteEarliestStartPerTicket)


The issue i am facing is i cant get it to return the earliest start time whihc in the example above would be 12/13/2023 8:30:06 AM


Intsead it is returning 1/1/2024  3:50:26 AM

Any help is greatly appreciated.

[v-kongfanf-msft]

Hi  @Anonymous_226 ,

Did @lbendlin  reply solve your problem? I tested the pbix file and everything works as expected! If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[lbendlin]

Most likely your column is formatted as text rather than DateTime.

[Anonymous_226]

@lbendlinthe column is formatted as General Date

*3/14/2001 1:30:55 PM (General Date)

[lbendlin]

[Anonymous_226]

@lbendlini took a look at your pbix and formating the column as earliest would work there but for my case i have to use a measure and accomplish this in DAX. I am connected Direct Query to dataset i am limited to DAX measures only. Do you have any other suggestions?

I have gotten close with this measure i have:

VAR StartTimesAsInteger =

ADDCOLUMNS(

FILTER(

SomeOtherTable,

SomeOtherTable[Value] = "Group 1"

),

"StartInteger",

YEAR(SomeOtherTable[Start]) * 10000000000 +

MONTH(SomeOtherTable[Start]) * 100000000 +

DAY(SomeOtherTable) * 1000000 +

HOUR(SomeOtherTable) * 10000 +

MINUTE(SomeOtherTable) * 100 +

SECOND(SomeOtherTable),

"UniqueKey",

SomeOtherTable[Number] & "|" & "Group 1"
)

VAR RankedStartTimes =

ADDCOLUMNS(

StartTimesAsInteger,

"Rank",

RANKX(

FILTER(

StartTimesAsInteger,

[UniqueKey] = EARLIER([UniqueKey])

),

[StartInteger],

,

ASC,

Dense

)

)

VAR EarliestStartTimes =

FILTER(

RankedStartTimes,

[Rank] = 1

)

RETURN COUNTROWS(EarliestStartTimes)

In this example i have 2 entries for group 1 that have the same number in the number column but i cant get the measure to return me the row with the earliest start time.

[lbendlin · Accepted solution]