Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all,
I need to calculate the sum of timedifference in below scenario:
I need to calculate the sum of difference between start date & end date of step = A (take minimum start date for each type) and Step = B (take minimum end date for each type). for example: difference between start date & end date of row 1 and 2, and difference between start date & end date of row 4 and 6. Record inside step are static (always A, B) but type can be more. In this case it will be 01:10:00
I am having difficulties due to blank values in start date and end date. Not able to pick the correct record.
can anyone give some suggestion?
Thanks
Solved! Go to Solution.
you can try this
Proud to be a Super User!
Hi @TaroGulati
Can you paste the data here ?? or share excel file with expected output. We want to know the exact output you are looking for..
I would be easier for us to find the proper solution if you provide output column as well .
Thanks!
Hi Here is the data and expected result in the last column:
Row Number | Step | Type | Start Date | End Date | Expected Result |
1 | A | X | 19-12-2024 11:10 | Row number 2 - Row number which is 1 hour | |
2 | B | X | 19-12-2024 12:10 | ||
3 | B | X | 19-12-2024 12:15 | ||
4 | A | Y | 19-12-2024 12:20 | Row number 4 - Row number 6 which is 10 minutes | |
5 | A | Y | 19-12-2024 12:25 | ||
6 | B | Y | 19-12-2024 12:30 |
Hi @TaroGulati ,
Here’s another solution using Power Query to calculate the sum of differences between the minimum Start Date for Step = A and minimum End Date for Step = B, grouped by Type.
The goal is to calculate the time difference between the earliest Start Date for Step = A and the earliest End Date for Step = B for each unique Type. The dataset has rows with blank values in either the Start Date or End Date, making it challenging to pick the correct records for the calculation.
We solve this by using Power Query in Power BI, grouping data by Type and calculating the differences directly.
let
Source = YourTable,
// Convert Start Date and End Date to datetime format
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Start Date", type datetime}, {"End Date", type datetime}}),
// Filter rows for Step A and Step B
FilteredRows = Table.SelectRows(#"Changed Type", each [Step] = "A" or [Step] = "B"),
// Group by Type and get the Min Start Date for Step A and Min End Date for Step B
GroupedTable = Table.Group(
FilteredRows,
{"Type"},
{
{"Min Start Date", each List.Min(Table.SelectRows(_, each [Step] = "A")[Start Date]), type datetime},
{"Min End Date", each List.Min(Table.SelectRows(_, each [Step] = "B")[End Date]), type datetime}
}
),
// Calculate the time difference in minutes
AddedDuration = Table.AddColumn(
GroupedTable,
"Difference (Minutes)",
each Duration.TotalMinutes([Min End Date] - [Min Start Date]),
type number
)
in
AddedDuration
I have attached an example pbix file for your reference.
Best regards,
Hi, thanks. I will try.
pls see if this is what you want
Proud to be a Super User!
Hi, Thanks for the response.
Solution purposed by you work but not completly in my case. Let me show you the complete case because in the above part i asked only the one part. As you can see below we have 4 columns: Category, Step, Type and start date. For each category i need to calculate start date differences. For each type (X,Y it can vary) take minimum start date for step A and minimum start date for step B. Once we have date difference for each category i need to calculate the sum and average based on types but should be easy. In the below mentioned case the sum for category ABC is 1 hour 10 minutes and for category XYZ is 40 minutes.
Row ID | Category | Step | Type | Start Date | Expected Result |
1 | ABC | A | X | 19-12-2024 11:10 | Time Difference is 1 Hour |
2 | ABC | B | X | 19-12-2024 12:10 | |
3 | ABC | B | X | 19-12-2024 12:15 | |
4 | ABC | A | Y | 19-12-2024 12:20 | Time Difference is 10 minutes |
5 | ABC | A | Y | 19-12-2024 12:25 | |
6 | ABC | B | Y | 19-12-2024 12:30 | |
7 | XYZ | A | X | 20-12-2024 15:00 | Time Difference is 10 minutes |
8 | XYZ | B | X | 20-12-2024 15:10 | |
9 | XYZ | B | X | 20-12-2024 15:30 | |
10 | XYZ | A | Y | 20-12-2024 15:35 | Time Difference is 30 minutes |
11 | XYZ | A | Y | 20-12-2024 16:00 | |
12 | XYZ | B | Y | 20-12-2024 16:05 |
I highlighted the rows:
Thanks in advance for the response
you can try this
Proud to be a Super User!
Hi, this is working.
Thanks
you are welcome
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
72 | |
62 | |
52 | |
48 |
User | Count |
---|---|
208 | |
89 | |
61 | |
59 | |
57 |