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
Anonymous
Not applicable

Help on a DAX Formula - Earliest and Latest evend for a day ana line

I would like some help to build a DAX formula that would collect for each day the earliest event - for each of the L.

 

Also another similar formula the latest event, in a manner I would be able to subtract the two and find the number of hours the line operates in each particular day..

 

Initially I was able to obtain it via MEASUREMENT, however, they do not how in the Format table and I could not create a subtraction formula between two measurements, reason I prefer a new column.

 

Thanks,

 

StartTime            StopTime             LineNum

4/3/2020 17:17  4/3/2020 17:17              3

4/3/2020 17:12  4/3/2020 17:12              1

4/3/2020 17:09  4/3/2020 17:09             2

4/3/2020 17:06  4/3/2020 17:06             3

4/3/2020 17:01  4/3/2020 17:01             2

4/3/2020 16:58  4/3/2020 16:58            3

4/3/2020 6:28    4/3/2020 6:28             2

4/3/2020 5:27    4/3/2020 5:27             3

4/3/2020 4:27    4/3/2020 4:27             1

4/2/2020 20:12  4/2/2020 20:16           3

4/2/2020 20:10  4/2/2020 20:14           1

4/2/2020 20:09  4/2/2020 20:14           2

4/2/2020 8:27    4/3/2020 8:40            1

4/2/2020 8:37    4/2/2020 8:39            2

4/2/2020 7:15    4/2/2020 7:18            3

1 ACCEPTED SOLUTION

@Anonymous I think I got your question, see attached pbix file. There are many tables in it, just focus on Line table and a page called Line.

 

You can take it from here and tweak as per your need. Would appreciate Kudos 🙂 if my solution helped.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous ,

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Anonymous
Not applicable

Hi Amit,

Sorry if my explanation was not clear.

See the example below, where per day and per each Line, we record the first and last event, so the date difference can be subtracted.

LineNum              Date      Start Time                           End Time             # of Hours

1              4/2/2020              4/2/20 8:27 AM                 4/3/20 8:40 AM                24

2              4/2/2020              4/2/20 8:37 AM                 4/2/20 8:14 PM                12

3              4/2/2020              4/2/20 7:15 AM                 4/2/20 8:16 PM                 13

1              4/3/2020              4/3/20 4:27 AM                 4/3/20 5:12 PM                 13

2              4/3/2020              4/3/20 6:28 AM                 4/3/20 5:09 PM                 11

3              4/3/2020              4/3/20 5:27 AM                 4/3/20 5:17 PM                 12

 

I got a solution already as per the second response, but do appreciate your help.

 

In the same kind of subject, one thing you can help.

There is any way we can replicate a "MEASUREMENT" in the Data Table (Edition) in a manner that it would show for us there and allow us to make other calculations with a measurement result?

@Anonymous I think I got your question, see attached pbix file. There are many tables in it, just focus on Line table and a page called Line.

 

You can take it from here and tweak as per your need. Would appreciate Kudos 🙂 if my solution helped.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi Parry,

your example was sufficient and helped me reach a solution for the problem.

 

The only other related tip would be in the # hours result, we can an Integer number for hours.  I can make it Minute also, but wha trick could we use to get HH: MM in the DIFF formula?

 

regards,

Leo

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.