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
IgorAM
Helper I
Helper I

Measure - Hours and dates

Hi guys,

 

I have two tables.

 

First table: Table with this columns about some calls: date and hour [dd:mm:YY hh:mm:ss], travel code, client, call duration [hh:mm:ss]

Second table: Table with some columns about the travels: travel code, start date travel [dd:mm:YY hh:mm:ss], end date travel [dd:mm:YY hh:mm:ss], start hours [hh:mm:ss], end hours [hh:mm:ss]

So I made a relationship between then, using travel code. Now I need to know the percent of call made during the travel using dax measure. Look this line for example:

 

First table: travel code: 250015; call started at 04/13 08:22:00; call ended 04/13 08:22:34
Second table: travel code: 250015; start travel 04/13 08:00; end travel 04/13 13:30

This travel should be counted. After count all of this cases, we should divide by the total of calls.

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

Hi, @IgorAM 

If you just want to get the percent of calls in the corresponding time period, please formulas like:

Count1 = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Call started]>=SUM(Table2[Start travel])&&Table1[Call ended]<=SUM(Table1[Call ended])))
All count = COUNT(Table1[Travel code])
Result = [Count1]/[All count]

Best Regards,
Community Support Team _ Eason

 

Thank you for the answer,

 

I think it's almost this, I tried this formulas, but it does not work. I'll give you another example:

 

 Call table:

 

Call_Date_Hour Travel CodeClientCall duration
14/04/2020 16:2418791668Cliente 100:00:36
19/04/2020 07:1318805913Cliente 100:00:59
19/04/2020 21:4818811065Cliente 100:00:36
19/04/2020 22:2318811065Cliente 100:00:33
19/04/2020 22:2518811065Cliente 100:00:31
23/04/2020 10:3018824537Cliente 100:00:50
23/04/2020 14:0618827367Cliente 100:00:30
23/04/2020 14:2318827651Cliente 100:01:02
23/04/2020 16:5218823981Cliente 100:00:43
24/04/2020 11:3518828915Cliente 100:00:58
24/04/2020 11:4118828915Cliente 100:00:44
24/04/2020 12:1018828915Cliente 100:00:32
24/04/2020 12:3618827367Cliente 100:00:32
24/04/2020 19:1918834085Cliente 100:00:32
24/04/2020 19:2818834085Cliente 100:00:30
25/04/2020 08:3218834940Cliente 100:00:36
25/04/2020 08:3318834940Cliente 100:00:33
26/04/2020 11:2618834085Cliente 100:00:36
08/05/2020 19:2618889171Cliente 989800:00:31
08/05/2020 19:3018889171Cliente 989800:00:31
09/05/2020 09:3518889171Cliente 989800:00:45
09/05/2020 12:0618889171Cliente 989800:00:36

 

Travels table:

 

Travel codeTravel_Date_startTravel_Date_end
187916682020-04-14 05:49:01.0002020-04-15 18:42:52.000
188059132020-04-16 18:54:38.0002020-04-23 14:23:45.000
188110652020-04-19 10:44:06.0002020-04-20 06:26:44.000
188245372020-04-23 05:18:45.0002020-04-23 11:30:48.000
188273672020-04-23 12:33:25.0002020-04-27 10:50:54.000
188276512020-04-23 13:11:03.0002020-04-23 14:25:51.000
188239812020-04-22 19:44:46.0002020-04-24 10:59:39.000
188289152020-04-23 18:07:51.0002020-04-24 12:10:48.000
188273672020-04-23 12:33:25.0002020-04-27 10:50:54.000
188340852020-04-24 18:05:08.0002020-04-26 18:32:09.000
188349402020-04-24 21:36:06.0002020-04-27 12:17:50.000
188891712020-05-09 09:27:30.0002020-05-12 23:08:40.000

 

We have to compare this two tables, travel by travel. For example: 

 

14/04/2020 16:2418791668Cliente 100:00:36

This call hapenned in the travel.

 

We know that because if we look for this travel code on the travel table, we'll find:

187916682020-04-14 05:49:01.0002020-04-15 18:42:52.000

 

The call was at 14/04 16:24, after the travel begins (14/04 05:49) and before it ends (15/04 18:42). So I have to count it.

 

Now, let's take another example:

 

08/05/2020 19:2618889171Cliente 989800:00:31

 

If we look for this travel code on the travel table, we'll find:

 

188891712020-05-09 09:27:30.0002020-05-12 23:08:40.000

 

The call was at 08/05 19:26, before the travel begins. In this case, I don't count this row on call table. If this call were after the travel ends, we don't need to call it too.

 

So I have to count the cases like the first example (travel 18791668) and divide by all the call we had. Then, I'll find a percentage.

 

The second example (travel 18889171) was just to show what we should not count.

v-easonf-msft
Community Support
Community Support

Hi, @IgorAM 

Not fully sure what you want. Can you share you expected result for further investigation?

You can try formulas as below:

 

diff1(seconds) = DATEDIFF(SUM(Table1[Call started]),SUM(Table1[Call ended]),SECOND)
diff2(seconds) = DATEDIFF(SUM(Table2[Start travel]),SUM(Table2[End travel]),SECOND)
Percentage = [diff1(seconds)]/[diff2(seconds)]

If I have misunderstood, please feel free to let me know.

 

Best Regards,
Community Support Team _ Eason

 

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