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.

tamerj1's avatar
tamerj1
Community Champion
3 years ago

sfernamer 
If I correctly understand, the following should work

Expected Result =
VAR CurrentTime1 = 'Table'[Time]
VAR CurrentTable =
    CALCULATETABLE (
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[GAme], 'Table'[Quarter] )
    )
VAR CurrentDefensive5Table =
    TOPN ( 1, TableBefore, 'Table'[Time] )
VAR CurrentTime2 =
    SUMX ( CurrentDefensive5Table, 'Table'[Time] )
VAR TableBefore =
    FILTER ( CurrentTable, 'Table'[Time] < CurrentTime )
VAR PreviousDefensive5Table =
    TOPN ( 1, TableBefore, 'Table'[Time] )
VAR PreviousTime =
    SUMX ( PreviousDefensive5Table, 'Table'[Time] )
RETURN
    CurrentTime2 - PreviousTime
sfernamer's avatar
sfernamer
Helper III
3 years ago

Hi tamerj1 

 

I tried your last code but it's not working. I'm not sure about the parts of "TableBefore" and "CurrentTime", bearing in mind the variables are not created before in the code. 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.