Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
TaroGulati
Helper III
Helper III

Dynamic sum Datetime difference

Hi all, 

 

I need to calculate the sum of timedifference in below scenario:

TaroGulati_0-1736746406930.png

 

 

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

1 ACCEPTED SOLUTION

@TaroGulati 

 

you can try this

 

Column =
VAR _start=minx(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])&&'Table'[Step]="A"&&'Table'[Type]=EARLIER('Table'[Type])),'Table'[Start Date])
var _end=minx(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])&&'Table'[Step]="B"&&'Table'[Type]=EARLIER('Table'[Type])),'Table'[Start Date])
return if ('Table'[Start Date]=_start,_end-'Table'[Start Date])
 
11.PNG
 
pls see the attachment below




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Uzi2019
Super User
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!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Hi Here is the data and expected result in the last column:

Row NumberStepTypeStart DateEnd DateExpected Result
1AX19-12-2024 11:10 Row number 2 - Row number which is 1 hour
2BX 19-12-2024 12:10 
3BX 19-12-2024 12:15 
4AY19-12-2024 12:20 Row number 4 - Row number 6 which is 10 minutes
5AY19-12-2024 12:25  
6BY 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

 

DataNinja777_0-1736751920234.png

I have attached an example pbix file for your reference.

 

Best regards,

Hi, thanks. I will try. 

@TaroGulati 

pls see if this is what you want

 

Column =
VAR _start=CALCULATE(min('Table'[Start Date]),ALLEXCEPT('Table','Table'[Type]))
var _end=CALCULATE(MIN('Table'[End Date]),ALLEXCEPT('Table','Table'[Type]))
return _end-_start
11.PNG
 
pls see the attachment below
 




Did I answer your question? Mark my post as a solution!

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 IDCategoryStepTypeStart DateExpected Result
1ABCAX19-12-2024 11:10Time Difference is 1 Hour
2ABCBX19-12-2024 12:10
3ABCBX19-12-2024 12:15
4ABCAY19-12-2024 12:20Time Difference is 10 minutes
5ABCAY19-12-2024 12:25
6ABCBY19-12-2024 12:30
7XYZAX20-12-2024 15:00Time Difference is 10 minutes
8XYZBX20-12-2024 15:10
9XYZBX20-12-2024 15:30
10XYZAY20-12-2024 15:35Time Difference is 30 minutes
11XYZAY20-12-2024 16:00
12XYZBY

20-12-2024 16:05

 

I highlighted the rows:

TaroGulati_0-1736752272287.png

Thanks in advance for the response

@TaroGulati 

 

you can try this

 

Column =
VAR _start=minx(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])&&'Table'[Step]="A"&&'Table'[Type]=EARLIER('Table'[Type])),'Table'[Start Date])
var _end=minx(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])&&'Table'[Step]="B"&&'Table'[Type]=EARLIER('Table'[Type])),'Table'[Start Date])
return if ('Table'[Start Date]=_start,_end-'Table'[Start Date])
 
11.PNG
 
pls see the attachment below




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi, this is working. 

Thanks

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.