Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sfernamer
Helper III
Helper III

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.

 

Data Structure.pngGrouped Defense.png

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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.

1.png2.png

View solution in original post

13 REPLIES 13
tamerj1
Super User
Super User

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.

1.png2.png

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.

 

Example1.png

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

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.

 

 

 

 

 

 

sfernamer
Helper III
Helper III

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 

 

 

sfernamer
Helper III
Helper III

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.

 

Example1.pngExample2.png

 

@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

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.

ExpectedResult.png

 

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

 

SumUp.png

 

 

 

 

Thank you in advance.

 

 

sfernamer
Helper III
Helper III

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

 

Exp_result.pngSumTime.png

 

 

@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

tamerj1
Super User
Super User

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
FreemanZ
Super User
Super User

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:

FreemanZ_0-1673066079492.png

FreemanZ_1-1673067304679.png

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.

sfernamer_0-1673471944246.png

 

 

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

 

sfernamer_1-1673471943431.png

 

 

 

 

Thank you in advance.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors