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 

 

Thank you for your reply. Looks like it works but not for all cases. I tested it and mark the wrong cells in yellow.

I would like to know how I could group by the obtained values. The calculation gives me the value for every single "Defensive Five" but there are some defensive fives that could be repeated. Like using GROUP BY in SQL.

 

Could you tell me, please, tamerj1 FreemanZ  ? Thank you in advance.

 

 

  • 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.