Forum Discussion
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" column:
- To find the difference between the cell below and the one above grouped by another columns (Game and Quarter).
I guess that I could use the DATEDIFF and LOOKUPVALUE but I'm not sure how I could use it bearing in mind that the Quarter starts witth value 10:00 and finish with 0:00.
To make the comprehension easier, I add a pic of the dataset (the first one) used and the calculation in Excel that offers the expected result (the one I want to get in Power BI with DAX).
The second one is a grouped sum after the calculation.
Thank you for your time and your knowledge.
13 Replies
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:
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.
Hi sfernamer
Please create a new calculated columnExpected Result = VAR CurrentTime = 'Table'[Time] VAR CurrentTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[GAme], 'Table'[Quarter] ) ) VAR TableAfter = FILTER ( CurrentTable, 'Table'[Time] > CurrentTime ) VAR NextTime = COALESCE ( MINX ( TableAfter, 'Table'[Time] ), CurrentTime ) RETURN NextTime - CurrentTimeHi tamerj1
Firstly, thank you for your time, looks like the solution 1 is working for what I wanted except for 1 special situation. Sometimes, there can be more than one row with the same Time_Def field (changing 2 defensive fives at the same h:mm:ss). Checking the calculation, it's calculating the difference for each of them despite the fact that one of this fields should be 0:00:00 (there is no difference). I don't know if we have to add a line of code to choose the first in order when there are some files with the same h:mm:ss.
After this, I would need to sum up like in a Pivot Table I shown above (Idk if it's possible with sumx).
Add you the example in Excel (in the same link) and the expected result in image to see if you could help me, please.
Thank you in advance.
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. 🙂
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
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.
sfernamer
If I correctly understand, the following should workExpected 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 - PreviousTimeHi 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.
I would like to add a sample of the dataset with 2 games and the columns related available to create the logic, to try to help. The Google Drive link to access to the Excel file: https://drive.google.com/drive/folders/1SlzwO6bgBBgrftwrVPGRVBE9KkIy_YqJ?usp=share_link
Thank you in advance tamerj1 / FreemanZ
