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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Taro_Gulat
Regular Visitor

Dynamic Date Difference

Hi all, 

 

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

Taro_Gulat_0-1736745579575.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

2 ACCEPTED SOLUTIONS
AMeyersen
Resolver III
Resolver III

If you are looking for a DAX measure, you could use a formula like this:

Duration in Minutes =
VAR _step_type =
    SUMMARIZECOLUMNS (
        'Table'[Type],
        "StartA",
            CALCULATE (
                MIN ( 'Table'[Start] ),
                'Table'[Step] = "A",
                ALLEXCEPT ( 'Table', 'Table'[Type] )
            ),
        "EndB",
            CALCULATE (
                MIN ( 'Table'[End] ),
                'Table'[Step] = "B",
                ALLEXCEPT ( 'Table', 'Table'[Type] )
            )
    )
RETURN
    SUMX ( _step_type, DATEDIFF ( [StartA], [EndB], MINUTE ) )

AMeyersen_1-1736757657715.png

 

View solution in original post

v-yilong-msft
Community Support
Community Support

Hi @Taro_Gulat ,

I create a table as you mentioned.

vyilongmsft_0-1736835939017.png

Then I think you can create a calculated column and here is the DAX code.

Column = 
VAR _MinStartDate =
    CALCULATE (
        MIN ( 'Table'[Start Date] ),
        FILTER (
            'Table',
            'Table'[Step] = "A"
                && 'Table'[Type] = EARLIER ( 'Table'[Type] )
        )
    )
VAR __MinEndDate =
    CALCULATE (
        MIN ( 'Table'[End Date] ),
        FILTER (
            'Table',
            'Table'[Step] = "B"
                && 'Table'[Type] = EARLIER ( 'Table'[Type] )
        )
    )
RETURN
    DATEDIFF ( _MinStartDate, __MinEndDate, MINUTE )

vyilongmsft_1-1736836021094.png

If you want to change the Column's format, you can do this step.

Column 2 = 
VAR _TotalTime =
    SUMX (
        SUMMARIZE ( 'Table', 'Table'[Type], "UniqueColumn", MAX ( 'Table'[Column] ) ),
        [UniqueColumn]
    )
RETURN
    FORMAT ( INT ( _TotalTime / 60 ), "00" ) & ":"
        & FORMAT ( MOD ( _TotalTime, 60 ), "00" ) & ":"
        & FORMAT ( 0, "00" )

vyilongmsft_2-1736836138080.png

 

 

Best Regards

Yilong Zhou

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

2 REPLIES 2
v-yilong-msft
Community Support
Community Support

Hi @Taro_Gulat ,

I create a table as you mentioned.

vyilongmsft_0-1736835939017.png

Then I think you can create a calculated column and here is the DAX code.

Column = 
VAR _MinStartDate =
    CALCULATE (
        MIN ( 'Table'[Start Date] ),
        FILTER (
            'Table',
            'Table'[Step] = "A"
                && 'Table'[Type] = EARLIER ( 'Table'[Type] )
        )
    )
VAR __MinEndDate =
    CALCULATE (
        MIN ( 'Table'[End Date] ),
        FILTER (
            'Table',
            'Table'[Step] = "B"
                && 'Table'[Type] = EARLIER ( 'Table'[Type] )
        )
    )
RETURN
    DATEDIFF ( _MinStartDate, __MinEndDate, MINUTE )

vyilongmsft_1-1736836021094.png

If you want to change the Column's format, you can do this step.

Column 2 = 
VAR _TotalTime =
    SUMX (
        SUMMARIZE ( 'Table', 'Table'[Type], "UniqueColumn", MAX ( 'Table'[Column] ) ),
        [UniqueColumn]
    )
RETURN
    FORMAT ( INT ( _TotalTime / 60 ), "00" ) & ":"
        & FORMAT ( MOD ( _TotalTime, 60 ), "00" ) & ":"
        & FORMAT ( 0, "00" )

vyilongmsft_2-1736836138080.png

 

 

Best Regards

Yilong Zhou

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

AMeyersen
Resolver III
Resolver III

If you are looking for a DAX measure, you could use a formula like this:

Duration in Minutes =
VAR _step_type =
    SUMMARIZECOLUMNS (
        'Table'[Type],
        "StartA",
            CALCULATE (
                MIN ( 'Table'[Start] ),
                'Table'[Step] = "A",
                ALLEXCEPT ( 'Table', 'Table'[Type] )
            ),
        "EndB",
            CALCULATE (
                MIN ( 'Table'[End] ),
                'Table'[Step] = "B",
                ALLEXCEPT ( 'Table', 'Table'[Type] )
            )
    )
RETURN
    SUMX ( _step_type, DATEDIFF ( [StartA], [EndB], MINUTE ) )

AMeyersen_1-1736757657715.png

 

Helpful resources

Announcements
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.