Forum Discussion

TaroGulati's avatar
TaroGulati
Helper III
1 year ago
Solved

Time difference total issue

Hi all,  I am having issue related to total for the below case: In the below table i need to calculate the time difference: For each category I have to identify last step based on time field...
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    alright i believe this should solve your issue. 

    TimeDifference =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Category],
        "TimeDiff",
            SWITCH (
                CALCULATE (
                    VALUES('Table'[Step]),
                    FILTER('Table', 'Table'[Time] = MAX('Table'[Time]))
                ),
                "Y",
                    DATEDIFF (
                        CALCULATE (
                            MIN('Table'[Time]),
                            'Table'[Step] = "X"
                        ),
                        CALCULATE (
                            MAX('Table'[Time]),
                            'Table'[Step] = "Y"
                        ),
                        SECOND
                    ),
                "X",
                    DATEDIFF (
                        CALCULATE (
                            MIN('Table'[Time]),
                            'Table'[Step] = "X"
                        ),
                        CALCULATE (
                            MAX('Table'[Time]),
                            'Table'[Step] = "Z"
                        ),
                        SECOND
                    ),
                "Z",
                    DATEDIFF (
                        CALCULATE (
                            MIN('Table'[Time]),
                            'Table'[Step] = "X"
                        ),
                        CALCULATE (
                            MAX('Table'[Time]),
                            'Table'[Step] = "Z"
                        ),
                        SECOND
                    )
            )
    ),
    [TimeDiff]
)
rohit1991's avatar
rohit1991
Super User
1 year ago

alright i believe this should solve your issue. 

TimeDifference =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Category],
        "TimeDiff",
            SWITCH (
                CALCULATE (
                    VALUES('Table'[Step]),
                    FILTER('Table', 'Table'[Time] = MAX('Table'[Time]))
                ),
                "Y",
                    DATEDIFF (
                        CALCULATE (
                            MIN('Table'[Time]),
                            'Table'[Step] = "X"
                        ),
                        CALCULATE (
                            MAX('Table'[Time]),
                            'Table'[Step] = "Y"
                        ),
                        SECOND
                    ),
                "X",
                    DATEDIFF (
                        CALCULATE (
                            MIN('Table'[Time]),
                            'Table'[Step] = "X"
                        ),
                        CALCULATE (
                            MAX('Table'[Time]),
                            'Table'[Step] = "Z"
                        ),
                        SECOND
                    ),
                "Z",
                    DATEDIFF (
                        CALCULATE (
                            MIN('Table'[Time]),
                            'Table'[Step] = "X"
                        ),
                        CALCULATE (
                            MAX('Table'[Time]),
                            'Table'[Step] = "Z"
                        ),
                        SECOND
                    )
            )
    ),
    [TimeDiff]
)
TaroGulati's avatar
TaroGulati
Helper III
1 year ago

Yes, this works. I understood what i was missing. Thanks