Forum Discussion

sfernamer's avatar
sfernamer
Helper III
3 years ago
Solved

Calculate Hour Type Column Difference

Hi everyone,   I text you because I'm trying to calculate the difference between the hour values of the same column. The objective is to find the sum of time for every value of "Defensive Five" col...
DATEDIFF
LOOKUPVALUE
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi sfernamer 
    Here is a sample file containing my two previously proposed solutions. You are right about the 2nd solution, there was sime mistakes. Not sure if either what you're looking for.

sfernamer's avatar
sfernamer
Helper III
3 years ago

Hi tamerj1  / FreemanZ 

 

Thank you for your replies. I would like to apologize because I made an error with the "expected result". What I want is to have the result bearing in mind the cell above, not below (add the example, with the correction). Could you help me, to modify the formula?

 

I tested your formulas and the FreemanZ gave me the expected result bearing in mind my mistake.

 

FreemanZ , I tested your 2n part of the formula but didn't work for me. As you can see in the 2nd image, the Sum_Time should be 1:32 but the calculation gives me more than 16 minutes. When I try to put the measure in mm:ss, the option is not allowed.

 

Thank you in advance. 🙂 

 

 

 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

sfernamer 

Please try

Expected Result =
VAR CurrentTime = 'Table'[Time]
VAR CurrentTable =
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[GAme], 'Table'[Quarter] )
)
VAR TableBefore =
FILTER ( CurrentTable, 'Table'[Time] < CurrentTime )
VAR PreviousTime =
MAXX ( TableBefore, 'Table'[Time] )
RETURN
CurrentTime - PreviousTime