topic Re: Calculation between 2 dates based on the same value in DAX Commands and Tips

Calculation between 2 dates based on the same value

MathieuF — Thu, 10 Dec 2020 10:51:35 GMT

Hello,

I am having difficulty implementing a calculation in Power BI.
I'm looking to calculate the time (Created_at) between the start of the action (Name: task.taken or task.resume) and the end of the action (Name: task.completed or task.transferred or task.expired_from_workbin) d 'an event (Task_id).

Created_at: date and time of the action
User_id: id of the individual
Name: nature of the action
Task_id: number of the event concerned by the action

How do you share the csv file?

Can you help me ?

Thanks in advance.

Mathieu

Re: Calculation between 2 dates based on the same value

AlB — Thu, 10 Dec 2020 12:21:39 GMT

Hi MathieuF

Best if you can share the pbix

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

And plese explain a bit more what exactly the result should look like. Is it in a visual per task_id? Something else?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Calculation between 2 dates based on the same value

MathieuF — Thu, 10 Dec 2020 13:30:47 GMT

Hello,
Thank you for your help.
Sorry my english is not good: D
I want to calculate the sum of time spent on actions between start and end per individual (user_id).
Here is my CSV: https://www.dropbox.com/s/qufloj2fob2srdc/journal_forum.csv?dl=0

and pbix https://www.dropbox.com/s/jl10cuf3qvceo3j/test_journal.pbix?dl=0

Mathieu

Re: Calculation between 2 dates based on the same value

AlB — Thu, 10 Dec 2020 13:38:42 GMT

MathieuF

1. Place user_id on a table visual

2. Place this measure in the table. It will give you the time in minutes. Change if you want it in another unit

Measure = SUMX ( DISTINCT ( Table1[task_id] ), VAR mint_ = CALCULATE ( MIN ( Table1[Created_at] ), Table1[Name] IN { "task.taken", "task.resume" } ) VAR maxt_ = CALCULATE ( MAX ( Table1[Created_at] ), Table1[Name] IN { "task.completed", "task.transferred", "task.expired_from_workbin" } ) RETURN ( maxt_ - mint_ ) * 24 * 60 )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Calculation between 2 dates based on the same value

MathieuF — Thu, 10 Dec 2020 14:01:54 GMT

AlB

I don't know if you saw, I put the pbix file.

Re: Calculation between 2 dates based on the same value

AlB — Thu, 10 Dec 2020 15:05:40 GMT

MathieuF

There's no task.expired_from_workbin in your data, only task.expired_from_step??

Had a quick look (see it all at work in the attached file). The measure seems to work fine for cases like ID

541fe1360f4ca114df0000bc

but is slightly off at ID

56e1917d7765624c6b0001fd

because task 5fcfa098bd0e54813b1340de appears in two instances ( taken-completed, resume-completed)

and yields negative numbers at cases like ID

4fc675c20f4ca11924008111

where the code works well but the data only has a task.taken for taskID 5fd0b96871336830f56f98f2, which messes the result up

Dealing with those cases will be trickier but the code can give you a good start. In any case you will need to exactly define all of the possible cases in which the data can appear and, equally important, define how to deal with them

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Calculation between 2 dates based on the same value

MathieuF — Thu, 10 Dec 2020 15:30:33 GMT

AlB

Yes it's normal. It is task.expired_from_workbin but it is not present in the example file.

The common point must be task_id and not user_id. The user_id is a filter.

Re: Calculation between 2 dates based on the same value

MathieuF — Thu, 17 Dec 2020 18:58:50 GMT

Hello @AIB,

Thank you for your help.
I have another question.
How do I make it take into account only the next action of the same task (task_id)?
Indeed, the 4 lines are the same tasks. Line 318 must go with line 376 and line 1202 with 2174.
With the given formula (and I thank you once again), calculate the time between line 318 and 2174, i.e. 155 minutes instead of 79 minutes (7 + 72).

Thanks in advance.

Mathieu

Re: Calculation between 2 dates based on the same value

MathieuF — Mon, 28 Dec 2020 16:10:33 GMT

Hello,

I advance a little bit ...

I managed to get the calculation of the first part but I cannot sum the 2 parts.

Namely from 8:44 am to 8:51 am + 10:08 am to 11:20 am.

7 + 72 min

How to do ?

Here is the new file.

Thanks in advance.

Mathieu

Fichiers: https://www.dropbox.com/sh/61ae2dtbmobl8tp/AAATpy0sRBMGRziU0Xe7Sy_Za?dl=0