Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I'm trying to calculate the amount of time a car spent in its assigned area, and the time spent outside of its area.
one of the problems is that there are areas that overlap and appear together.
I have a table that contains the car plate, the actual area he was at and the timestamp, the assigned area, and the time he spent between each row:
in this example, the assigned area is area 1, and I want that the calculation will be something like: sum(time_difference) if area column contains assigned_area column.
so the result should be in this example is:
time in assigned area : sum (rows 869-883) = 00:13:55
time spent outside the area: sum (884-888) = 00:05:00,
in a table like this:
please help me! thank you 🙂
Solved! Go to Solution.
Hi @bar1694
You can first add two columns as below. Change "Date only" column to Date type.
Date only = DATEVALUE([Date])
Is in assigned area = IF(CONTAINSSTRING('Table'[area],'Table'[assigned_area]),1,0)
Then create a new table with below DAX. This will return you a table with summarized time in minute unit.
Table 2 =
SUMMARIZE (
'Table',
'Table'[Date only],
'Table'[Plate],
'Table'[assigned_area],
"time in assigned area",
CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 1 ) * 24 * 60,
"time spent outside the area",
CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 0 ) * 24 * 60
)
If you want it to return in "hh:mm:ss" format, try
Table 2 =
SUMMARIZE (
'Table',
'Table'[Date only],
'Table'[Plate],
'Table'[assigned_area],
"time in assigned area",
FORMAT (
CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 1 )
+ TIME ( 0, 0, 0 ),
"hh:mm:ss"
),
"time spent outside the area",
FORMAT (
CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 0 )
+ TIME ( 0, 0, 0 ),
"hh:mm:ss"
)
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @bar1694
You can first add two columns as below. Change "Date only" column to Date type.
Date only = DATEVALUE([Date])
Is in assigned area = IF(CONTAINSSTRING('Table'[area],'Table'[assigned_area]),1,0)
Then create a new table with below DAX. This will return you a table with summarized time in minute unit.
Table 2 =
SUMMARIZE (
'Table',
'Table'[Date only],
'Table'[Plate],
'Table'[assigned_area],
"time in assigned area",
CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 1 ) * 24 * 60,
"time spent outside the area",
CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 0 ) * 24 * 60
)
If you want it to return in "hh:mm:ss" format, try
Table 2 =
SUMMARIZE (
'Table',
'Table'[Date only],
'Table'[Plate],
'Table'[assigned_area],
"time in assigned area",
FORMAT (
CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 1 )
+ TIME ( 0, 0, 0 ),
"hh:mm:ss"
),
"time spent outside the area",
FORMAT (
CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 0 )
+ TIME ( 0, 0, 0 ),
"hh:mm:ss"
)
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @bar1694 ,
Can you please share a sample dataset of your example above that can be copy pasted in MS Excel. Thanks
sure,
Plate | area | Date | assigned_area | Time Difference (hh:mm:ss) | Time Difference |
6583880 | area 1 | 5/16/2022 10:17 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1 | 5/16/2022 10:18 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1 | 5/16/2022 10:19 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1 | 5/16/2022 10:20 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1 | 5/16/2022 10:21 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1 | 5/16/2022 10:22 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1 | 5/16/2022 10:23 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1, area 4 | 5/16/2022 10:24 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1, area 4 | 5/16/2022 10:25 | area 1 | 00:01:01 | 0.000706019 |
6583880 | area 1, area 4 | 5/16/2022 10:26 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1, area 4 | 5/16/2022 10:26 | area 1 | 00:00:11 | 0.000127315 |
6583880 | area 1, area 4 | 5/16/2022 10:28 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1, area 4 | 5/16/2022 10:48 | area 1 | 00:00:43 | 0.000497685 |
6583880 | area 1, area 4 | 5/16/2022 10:50 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 1, area 4 | 5/16/2022 10:51 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 4 | 5/16/2022 10:52 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 4 | 5/16/2022 10:53 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 4 | 5/16/2022 10:54 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 4 | 5/16/2022 10:55 | area 1 | 00:01:00 | 0.000694444 |
6583880 | area 4 | 5/16/2022 10:56 | area 1 | 00:01:00 | 0.000694444 |
thank you very much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |