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

Please cross check for any filters on the page or the visual.
If it still continues to persist. Please share the file in DM if possible so i can have  a  look at it,

  • 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