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
Krzysiek87
Helper II
Helper II

Dax TIME restaurant transaction

I couldn't find a similar topic in the forum, so maybe someone can help me. I mean, I work in the restaurant sector. Document = Transactions, now we want to track the differences between account opening and closing times to see if employees are honest. The problem arises because it can not be calculated with a simple formula DateDIFF why?? because each receipt can have several items, as you know, you go to rest, you order many things, and for each item in the database there is a time which is then summed up and comes out stupid. Time DIFF measure is the stupid thing I came up with unfortunately 😞 As I calculated it is a simple formula in excel, the times are correct, however, I do not know how to build the measure so that it was counted correctly. I am adding screens so that you can see what data I have. Where it is " simple excel time formula" as you can see the values are reasonably correct but not. I will be much obliged for help how to solve this problem.

 

Krzysiek87_0-1675771652102.png

 

1 ACCEPTED SOLUTION

Hi,

Using your data I made a pbix with 2 solutions :

1/ Using Power Query to group by DOCUMENT number and calculate duration in a calculated column. Very easy and quick. (see my Query Tickets Grouped)

2/ Using a mesure in DAX with a formula that looks like :

 

Mesure In DAX Duration = 
VAR BeginTicket = MIN ( TableTickets[Open DOC HOUR] )
VAR EndTicket = MAX( TableTickets[CLOSE DOC HOUR] )
VAR CurrTicket = SELECTEDVALUE( TableTickets[DOCUMENT] )//means Take the document number on the row of the table
RETURN
CALCULATE( EndTicket-BeginTicket , TableTickets[DOCUMENT]=CurrTicket )
 
You can then use the format menu of the column to display it as you want or create your own format. Using n for minutes (m is for month), so FORMAT( [Duration], "nn" ) will give you a time in minutes with 2 digits.
You'll find attached my Excel files with your data and the pbix file with solution in DAX and in Power Query.
Let us know if it works

View solution in original post

11 REPLIES 11
AilleryO
Memorable Member
Memorable Member

Hi,

 

It would help if you could provide sample data as a table we can copy, or download, so we can play around your data and maybe understand your exact needs...
One calculation per order ? With min and max time ?

 

I can't add file because "xlsx" and PBIX is nos supported i tget comment

Add them in a zip file, or at least copy and paste data in a table in your message so we can copy and paste data as well from your message and not typed them in.

Thanks

Krzysiek87_0-1675922604059.png

 

Krzysiek87_1-1675922620868.png

 

 

Sorry I cannot do anything with an image, can you paste as a table not as an image.

Thanks

As you can see, the table is not difficult, more or less the problem is that I need to know the "measure" to calculate the time between opening and closing the document, the problem is that there are sometimes many items to 1 document 😞

The point is to calculate exactly what is the time between opening and closing the document, but so that the time when there are more items does not add up, so that it is the real value of the delay.

DOCUMENTRESTAURANTEXEC DATEYEARMONTHWEEK DAYTowNazwaGrupaAdmOpen DOC HOURCLOSE DOC HOUR
PAR/389Restauracja Focaccia2023-01-0620231FridayHERBATA ROZGRZWAJĄCA CZARNA FOCNAPOJE GORĄCE06-01-23 18:1606-01-23 18:20
PAR/389Restauracja Focaccia2023-01-0620231FridayHERBATA ROZGRZWAJĄCA CZARNA FOCNAPOJE GORĄCE06-01-23 18:1606-01-23 18:20
PAR/393Restauracja Focaccia2023-01-0620231FridayHERBATA ROZGRZWAJĄCA CZARNA FOCNAPOJE GORĄCE06-01-23 18:3106-01-23 18:35
PAR/393Restauracja Focaccia2023-01-0620231FridayHERBATA ROZGRZWAJĄCA CZARNA FOCNAPOJE GORĄCE06-01-23 18:3106-01-23 18:35
PAR/402Restauracja Focaccia2023-01-0620231FridayHERBATA ROZGRZWAJĄCA CZARNA FOCNAPOJE GORĄCE06-01-23 19:1106-01-23 19:13
PAR/391Restauracja Focaccia2023-01-0620231FridayHERBATA ROZGRZWAJĄCA CZARNA FOCNAPOJE GORĄCE06-01-23 18:2106-01-23 18:30
PAR/391Restauracja Focaccia2023-01-0620231FridayHERBATA ROZGRZWAJĄCA CZARNA FOCNAPOJE GORĄCE06-01-23 18:2106-01-23 18:30
PAR/396Restauracja Focaccia2023-01-0620231FridayHERBATA ROZGRZWAJĄCA CZARNA FOCNAPOJE GORĄCE06-01-23 18:3806-01-23 18:48

Hi,

Using your data I made a pbix with 2 solutions :

1/ Using Power Query to group by DOCUMENT number and calculate duration in a calculated column. Very easy and quick. (see my Query Tickets Grouped)

2/ Using a mesure in DAX with a formula that looks like :

 

Mesure In DAX Duration = 
VAR BeginTicket = MIN ( TableTickets[Open DOC HOUR] )
VAR EndTicket = MAX( TableTickets[CLOSE DOC HOUR] )
VAR CurrTicket = SELECTEDVALUE( TableTickets[DOCUMENT] )//means Take the document number on the row of the table
RETURN
CALCULATE( EndTicket-BeginTicket , TableTickets[DOCUMENT]=CurrTicket )
 
You can then use the format menu of the column to display it as you want or create your own format. Using n for minutes (m is for month), so FORMAT( [Duration], "nn" ) will give you a time in minutes with 2 digits.
You'll find attached my Excel files with your data and the pbix file with solution in DAX and in Power Query.
Let us know if it works
v-binbinyu-msft
Community Support
Community Support

Hi @Krzysiek87 ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

 

Best regards,
Community Support Team_Binbin Yu

Hi thx for answer screan from data look like that. 

 

Krzysiek87_1-1675836492113.png

 

Krzysiek87
Helper II
Helper II

any one ?? :((

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.

Top Solution Authors