topic Re: DAX - Getting Earliest Date Time in DAX Commands and Tips

DAX - Getting Earliest Date Time

Anonymous_226 — Wed, 27 Mar 2024 19:45:49 GMT

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.

Re: DAX - Getting Earliest Date Time

lbendlin — Thu, 28 Mar 2024 17:38:44 GMT

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

Re: DAX - Getting Earliest Date Time

Anonymous_226 — Thu, 28 Mar 2024 18:05:44 GMT

lbendlinthe column is formatted as General Date

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

Re: DAX - Getting Earliest Date Time

lbendlin — Thu, 28 Mar 2024 18:14:51 GMT

Re: DAX - Getting Earliest Date Time

Anonymous — Fri, 29 Mar 2024 08:43:33 GMT

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

Re: DAX - Getting Earliest Date Time

Anonymous_226 — Mon, 01 Apr 2024 18:28:13 GMT

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.

Re: DAX - Getting Earliest Date Time

lbendlin — Mon, 01 Apr 2024 19:05:16 GMT