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.

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi sfernamer 

Not sure if i fully get your, you may

1) add a column like this:

Result = 
VAR _time =
MINX(
    FILTER(
        TableName,
        TableName[Game]=EARLIER(TableName[Game])
            &&TableName[Quarter]=EARLIER(TableName[Quarter])
            &&TableName[Time]>EARLIER(TableName[Time])
    ),
    TableName[Time]
) - [Time]
RETURN
IF([Time]=TIME(10,0,0), TIME(0,0,0), _time)

2) write a measure like this:

SumTime = 
CONVERT(
    SUMX(
        TableName,
        CONVERT(TableName[Result], DOUBLE)
    ),
    DATETIME
)

i tried with a simplified dataset and it worked like this:

sfernamer's avatar
sfernamer
Helper III
3 years ago

Hi FreemanZ 

 

I tried your code and worked but I made a mistake when defining the problem. I didn't need that the cell contained the calculation like the way I told, I need that the cell contains the difference between the line in the same row and the row above, bearing in mind the change of quarter and the change in game.

 

All data is located in the same table. If you feel more comfortable, you can take the Excel in the lines below where I show you a piece of the dataset and the expected results.

 

The Google Drive Link: https://drive.google.com/drive/folders/1SlzwO6bgBBgrftwrVPGRVBE9KkIy_YqJ?usp=share_link 

 

Example of the expected Result with a Game (ExpectedResult Tab in xlsx). The ExpectedResult is calculated with the difference between the cell and the one above, bearing in mind the end of quarters or the change in games.

 

 

Once we could calculate the ExpectedResult value, I would like to sum up the value (like it was a Pivot Table in Excel):

 

 

 

 

 

Thank you in advance.