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

Calculation of a duration with filters for certain values using only the oldest one

Hello everybody, 

 

thank you all for helping each other, I already learned a lot by reading a few posts. Now I hit a wall with a problem and hope someone can point me in the right direction. 

 

I want to calculate a duration between two dates of certain actions. The issue is, my source combines several actions and actions can be repeated, but I only want to calculate the duration from the first action. As Background: We report content to certain players and also send reminders, the system collects all dates from each message. To calculate how long a case was open, we calculate the finish date minus the first action date. 

 

The table I have looks something like this: 

item idaction idaction dateitem finishedaction name
8245405.02.2019 10:42:0020.02.2019 15:13ISP
8224501.02.2019 11:59:0020.02.2019 15:13Service
824008.01.2019 16:30:4520.02.2019 15:13ISP
100103.01.2019 10:41:5211.01.2019 11:01ICCAM
1002907.01.2019 15:55:5311.01.2019 11:01ISP

 

So I would like to calculate the average time for the ISP action, using the oldest one in action Date and substracting that from the finished time. In this example I would calculate the duration of action ID 40 and 29 and create an average. 

I can think of either creating somehow a new tabe which only gives me the first date for the ISP action or calculate it with a row of "if" filters which gives me the duration at each line which I then can filter later. Has anyone any idea and is the issue clear or do you need something else to help? 

Thank you so much in advance"

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

Hi @Anonymous ,

 

According to your description, you want to get the average duration between the first action datetime when action name ="ISP" and the finished datetime of items ,right?

 

If so, you could use the following formula to get all seconds between two time:

Duration(seconds) = 
var _first=MINX(FILTER(ALL('Table'),[action name]="ISP" && [item id]=MAX('Table'[item id])),[action date])

RETURN (MAX('Table'[item finished])-_first)*60*60*24

Then to get the average:

Average = 
var _t=SUMMARIZE('Table','Table'[item id],"Duration",[Duration(seconds)])
return AVERAGEX(_t,[Duration])

 

Output:

Eyelyn9_0-1647913020754.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-eqin-msft
Community Support
Community Support

Hi @Anonymous ,

 

According to your description, you want to get the average duration between the first action datetime when action name ="ISP" and the finished datetime of items ,right?

 

If so, you could use the following formula to get all seconds between two time:

Duration(seconds) = 
var _first=MINX(FILTER(ALL('Table'),[action name]="ISP" && [item id]=MAX('Table'[item id])),[action date])

RETURN (MAX('Table'[item finished])-_first)*60*60*24

Then to get the average:

Average = 
var _t=SUMMARIZE('Table','Table'[item id],"Duration",[Duration(seconds)])
return AVERAGEX(_t,[Duration])

 

Output:

Eyelyn9_0-1647913020754.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hey @v-eqin-msft 

thank you very much. I tried it and at first it did not work, or gave me other numbers than mine but than I found one more filter I had running and this gives me the same numbers like my complicated solution, while yours is quite pretty and easy to implement and does not need a new column. 

 

So thank you very much for your help!

Anonymous
Not applicable

So I kind of solved it, but not in a pretty way. 

For those interested, I duplicated the table and did a "group by" item_id and as function a min on action date. Then created a relation to other tables and "imported" the information via the related function. Now I filtered via action ISP and average on the new field. I believe that gives me the Data I need. I would have loved to have it solved within the same table or with one function.

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.