topic Re: Subtract one time from another in DAX Commands and Tips

Subtract one time from another

82186 — Fri, 28 Jun 2024 12:12:13 GMT

The database has a counter for recording the time of signals (Date of recording) There is a machine and this machine sends a signal from the beginning of the cut (LD_11_Cnt_Saw1_Cut Start) and its completion (LD11_Cnt_Saw2_CutDone).

How can I make a request on DAX in which the final time will subtract the initial time and show the time of the cut itself?

Re: Subtract one time from another

lbendlin — Sun, 30 Jun 2024 14:33:45 GMT

Subtract the two values and then use FORMAT to display the duration in a meaningful way.

Re: Subtract one time from another

82186 — Sun, 30 Jun 2024 19:58:33 GMT

I understand that they need to be subtracted, but I don't understand how to do it. Can you send me a sample code?

Re: Subtract one time from another

lbendlin — Sun, 30 Jun 2024 21:03:56 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Subtract one time from another

82186 — Mon, 01 Jul 2024 08:12:04 GMT

ID	Date of recording	Unit number	The name
1	28.06.2024 12.52.23	13	LD11_Cnt_Saw2_CutDone
2	28.06.2024 13.11.01	13	LD11_Cnt_Saw2_CutDone
3	28.06.2024 12.47.54	46	LD_11_Cnt_Saw2_CutStart
4	28.06.2024 13.06.15	46	LD_11_Cnt_Saw2_CutStart

Re: Subtract one time from another

82186 — Mon, 01 Jul 2024 08:34:31 GMT

Is this information enough?

(Date of recording) - Date of recording
(Unit number) - unique number
(The name) - Signal name

There are two signals:
(LD_11_Cnt_Saw2_Cut Start) - start cutting
(LD11_Cnt_Saw2_Cut Done) - completion of the cut

You need to subtract (LD11_Cnt_Saw2_Cut Done) from (LD_11_Cnt_Saw2_Cut Start)

Thank you very much in advance!

Re: Subtract one time from another

lbendlin — Mon, 01 Jul 2024 12:29:46 GMT

Your sample data is inconsistent. Please validate and correct.

Re: Subtract one time from another

82186 — Tue, 02 Jul 2024 10:28:21 GMT

Hello In the screenshot, you have displayed the table absolutely correctly. I have the same one. It is necessary to subtract the beginning of the cut (LD_11_Cnt_Saw2_Cut Start) from the end of the cut (ld11_cnt_saw2_cut) and thereby get the time for which the saw made the cut.

If necessary, I can send the file itself. But it will have the same thing as in the screenshot

Re: Subtract one time from another

lbendlin — Tue, 02 Jul 2024 15:34:47 GMT

The ID column in your sample was out of sequence. Should it be ignored and the sorting be done based on timestamp instead?

Re: Subtract one time from another

82186 — Tue, 02 Jul 2024 16:24:44 GMT

Here is the file: https://www.dropbox.com/scl/fi/yhs3bcrb87bxq6ueo6byx/test.pbix?rlkey=gzhf02jc1wjwq78tti9b2s9fu&st=jc2ao4o7&dl=0

The id field will always be unique...

Re: Subtract one time from another

lbendlin — Wed, 03 Jul 2024 00:31:53 GMT

I don't understand how Unit number correlate with "The name" items.

Note that your datetime slicers will not work as long as they come from the same table.

Re: Subtract one time from another

82186 — Wed, 03 Jul 2024 08:07:40 GMT

What can you do then?

Re: Subtract one time from another

lbendlin — Wed, 03 Jul 2024 11:49:08 GMT

Use disconnected tables for your slicers.

Re: Subtract one time from another

82186 — Wed, 03 Jul 2024 15:31:51 GMT

Can you please send an example?

Re: Subtract one time from another

lbendlin — Thu, 04 Jul 2024 20:08:53 GMT

example of what?

Duplicate your table, or create separate tables for the time ranges.

What is your business users' expectation? Have they expressed how they would like to pick the date ranges? Are they concerned about the amount of screen space taken up by your slicers?