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
phodges
Frequent Visitor

Time calculations using max/min showing backwards

I wrote DAX functions to show the beginning (using MIN()) and ending (using MAX()) times of events occurring within a test, with different tests denoted by a Test ID and different events noted by an Event ID. For some of the Test IDs the measures work to give me the correct beginning and ending times of the events. However, for some of the Test IDs, the event beginning and end times are showing backwards, i.e. what should be the min time is showing as the max time and vice versa. The table below shows a sample of data. To note: Event ID Manual and Event ID Raw are basically the same thing with a few differences. The formatting of the sample data is similar to the actual data, so though it may look strange it is correct.

 

Test IDEvent ID ManualEvent ID RawEvent PointDateTime
111110/1/201906:00:00AM
111210/1/201906:01:00AM
111310/1/201906:02:00AM
111410/1/201906:03:00AM
111510/1/201906:04:00AM
111610/1/201906:05:00AM
1     
1     
122110/1/201907:00:00AM
122210/1/201907:01:00AM
122310/1/201907:02:00AM
122410/1/201907:03:00AM
122510/1/201907:04:00AM
122610/1/201907:05:00AM
1     
1     
133110/1/201908:00:00AM
133210/1/201908:01:00AM
133310/1/201908:02:00AM
133410/1/201908:03:00AM
133510/1/201908:04:00AM
133610/1/201908:05:00AM
2 1110/10/201906:00:00AM
211210/10/201906:01:00AM
211310/10/201906:02:00AM
211410/10/201906:03:00AM
211510/10/201906:04:00AM
2 1610/10/201906:05:00AM
2     
2 2110/10/201907:00:00AM
222210/10/201907:01:00AM
222310/10/201907:02:00AM
222410/10/201907:03:00AM
222510/10/201907:04:00AM
2 2610/10/201907:05:00AM
311110/1/201906:00:00AM
311210/1/201906:01:00AM
311310/1/201906:02:00AM
311410/1/201906:03:00AM
311510/1/201906:04:00AM
311610/1/201906:05:00AM
3     
3     
322110/1/201907:00:00AM
322210/1/201907:01:00AM
322310/1/201907:02:00AM
322410/1/201907:03:00AM
322510/1/201907:04:00AM
322610/1/201907:05:00AM
3     
3     
333110/1/201908:00:00AM
333210/1/201908:01:00AM
333310/1/201908:02:00AM
333410/1/201908:03:00AM
333510/1/201908:04:00AM
333610/1/201908:05:00AM
3     
344110/1/201909:00:00AM
344210/1/201909:01:00AM
344310/1/201909:02:00AM
344410/1/201909:03:00AM
344510/1/201909:04:00AM
344610/1/201909:05:00AM

 

If anyone has experienced this issue or knows how to fix it, any help would be very appreciated.

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@phodges ,

 

You may modify your measure using dax like pattern below:

Begin Time Current Event =
VAR Currentevent =
    MAX ( 'Sheet1'[Event ID] )
VAR Currenteventbeginningtime =
    CALCULATE (
        MIN ( 'Sheet1'[Date and Time] ),
        FILTER ( 'Sheet1', 'Sheet1'[Event ID] = Currentevent )
    )
RETURN
    Currenteventbeginningtime
End Time Previous Event =
VAR Previousevent =
    MAX ( 'Sheet1'[Event ID] ) - 1
VAR Previouseventendingtime =
    CALCULATE (
        MAX ( 'Sheet1'[Date and Time] ),
        FILTER ( 'Sheet1', 'Sheet1'[Event ID] = Previousevent )
    )
RETURN
    Previouseventendingtime

 

Regards,

Jimmy Tao

View solution in original post

8 REPLIES 8
phodges
Frequent Visitor

Thank you Jimmy! I was actually working on it some more yesterday and the DAX below made it work:

 

Begin Time Current Event =
var TestID = MAX('Sheet1'[Test ID])
var Currentevent= max('Sheet 1'[Event ID Raw])
var Currenteventbegintime = CALCULATE(MAX('Sheet 1'[Date and Time]), 'Sheet 1'[Event ID Raw] = Currentevent, 'Sheet 1'[Test ID] = TestID)
return Currenteventbegintime
 
End Time Previous Event =
var TestID = MAX('Sheet1'[Test ID])
var Previousevent = max('Sheet 1'[Event ID Raw]) - 1
var Previouseventendtime = CALCULATE(MAX('Sheet 1'[Date and Time]), 'Sheet 1'[Event ID Raw] = Previousevent, 'Sheet 1'[Test ID] = TestID)
return Previouseventendtime
 
I think what it needed was the Test ID as a filter as well. I was assuming that the filter would be applied to the whole page, but it looks like it needed to be called in the measured. Thank you so much for your help and time!

@phodges, You need to move out the final formula from var and try like this

Begin Time Current Event =
Currenteventbeginningtime = CALCULATE(MIN('Sheet1'[Date and Time]), 'Sheet1'[Event ID] = max('Sheet1'[Event ID]))


End Time Previous Event =
Previouseventendingtime= CALCULATE(MAX('Sheet1'[Date and Time]), 'Sheet1'[Event ID] = max('Sheet1'[Event ID]) - 1)

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

@amitchandak,

 

Thank you so much for your reply, and I am sorry for my very late reply. I did not receive a notification that you had replied. There is some sample data at the beginning of this forum post, but the solution has already been found. Either solution, the one @v-yuta-msft provided or the one I provided works, though for the earlier Test ID still needs to be added as a filter. I think that was my main problem, not including Test ID as a filter. But thank you for the help! I very much appreciate you reaching out and for being willing to help.

@phodges , Thanks for the update. Please accept the solution that has worked for you.

v-yuta-msft
Community Support
Community Support

@phodges ,

 

You may modify your measure using dax like pattern below:

Begin Time Current Event =
VAR Currentevent =
    MAX ( 'Sheet1'[Event ID] )
VAR Currenteventbeginningtime =
    CALCULATE (
        MIN ( 'Sheet1'[Date and Time] ),
        FILTER ( 'Sheet1', 'Sheet1'[Event ID] = Currentevent )
    )
RETURN
    Currenteventbeginningtime
End Time Previous Event =
VAR Previousevent =
    MAX ( 'Sheet1'[Event ID] ) - 1
VAR Previouseventendingtime =
    CALCULATE (
        MAX ( 'Sheet1'[Date and Time] ),
        FILTER ( 'Sheet1', 'Sheet1'[Event ID] = Previousevent )
    )
RETURN
    Previouseventendingtime

 

Regards,

Jimmy Tao

phodges
Frequent Visitor

Hello again Jimmy,

 

Sorry, but I have to clarify an error. The Event ID in the measures above should be Event ID Raw, not just Event ID. The slicer I use is also Event ID Raw. I apologize for that confusion, but thank you again for looking into this.

phodges
Frequent Visitor

Hello Jimmy,

 

Thank you for your response. I am trying to get the beginning time of a current Event ID within a specific Test ID and am using the MIN() function to get it. I also want to get the ending time of the previous Event ID (from the same Test ID) and am using the MAX() function to get it. However, for some Test IDs, I get the opposite, I use MIN() to get the beginning time of the current Event ID and instead get the ending time of the current Event ID, and I use MAX() to get the ending time of the previous Event ID and instead get the beginning time of the previous Event ID. This only happens for some of the Test IDs, meaning for all Event IDs in a specific Test ID the times show backwards as decribed above. For other Test IDs, everything works as expected.

 

Here is the measure I use to get the beginning time of the current Event ID:

Begin Time Current Event =
var Currentevent = max('Sheet1'[Event ID])
var Currenteventbeginningtime = CALCULATE(MIN('Sheet1'[Date and Time]), 'Sheet1'[Event ID] = Currentevent)
return Currenteventbeginningtime
 

Here is the measure I use to get the ending time of the previous Event ID:

End Time Previous Event =
var Previousevent= max('Sheet1'[Event ID]) - 1
var Previouseventendingtime= CALCULATE(MAX('Sheet1'[Date and Time]), 'Sheet1'[Event ID] = Previousevent)
return Previouseventendingtime
 
Note: 'Sheet1'[Date and Time] (not shown in the sample data above) is a column I added that combined the Date and Time columns from the sample data above
 
Thank you in advance for your help with this issue!
 
P.S. I am using two slicers, one with Test ID and another with Event ID, Test ID to call the specific test and Event ID to call the specific event in that test.
v-yuta-msft
Community Support
Community Support

@phodges ,

 

"However, for some of the Test IDs, the event beginning and end times are showing backwards, i.e. what should be the min time is showing as the max time and vice versa."

 

I'm confused on this description, could you please clarify more details on your issue?

 

Regards,

Jimmy Tao

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.