topic Re: Calculate Hour Type Column Difference in DAX Commands and Tips

Calculate Hour Type Column Difference

sfernamer — Fri, 06 Jan 2023 21:40:38 GMT

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.

Re: Calculate Hour Type Column Difference

FreemanZ — Sat, 07 Jan 2023 04:55:54 GMT

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:

Re: Calculate Hour Type Column Difference

tamerj1 — Sat, 07 Jan 2023 08:34:25 GMT

Hi sfernamer
Please create a new calculated column

Expected 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 - CurrentTime

Re: Calculate Hour Type Column Difference

sfernamer — Sat, 07 Jan 2023 20:11:43 GMT

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

Re: Calculate Hour Type Column Difference

tamerj1 — Sat, 07 Jan 2023 20:17:28 GMT

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

Re: Calculate Hour Type Column Difference

sfernamer — Mon, 09 Jan 2023 21:21:48 GMT

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.

Re: Calculate Hour Type Column Difference

sfernamer — Mon, 09 Jan 2023 21:50:31 GMT

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

Re: Calculate Hour Type Column Difference

tamerj1 — Tue, 10 Jan 2023 15:41:24 GMT

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

Re: Calculate Hour Type Column Difference

sfernamer — Wed, 11 Jan 2023 21:10:47 GMT

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.

Re: Calculate Hour Type Column Difference

sfernamer — Wed, 11 Jan 2023 21:21:57 GMT

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

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.

Re: Calculate Hour Type Column Difference

tamerj1 — Thu, 12 Jan 2023 02:30:12 GMT

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.

Re: Calculate Hour Type Column Difference

sfernamer — Sat, 14 Jan 2023 16:01:41 GMT

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

Re: Calculate Hour Type Column Difference

tamerj1 — Sat, 14 Jan 2023 16:23:21 GMT

sfernamer
That can be done but you need a sort order as it seems that defensive five don't have a specific order. Please advise.

Re: Calculate Hour Type Column Difference

sfernamer — Sat, 14 Jan 2023 21:00:18 GMT

Hi tamerj1

Firstly, thank you for your reply. That's true that I don't have a column with a specific order but, bearing in mind, we talk about game plays, I can add an index column (the order of the games is always the same, like the one shown in "Testing.xlsx").

I added it for the game with the specific problem to help you to test the change (it's a extraction of the dataset with a column index added). The name of the folder is "Testing.xlsx" with the extraction of one game with the specific problem (different values for "Defensive Five" for the same "Time_Def" column value). I highlighted in yellow the specific rows that are problem for the code (it's not recurrent but can happen in some games).

I guess it's a change in the code already working but I tried different options but not working. Could you help me, please?

Thank you in advance.