Difference between dates on different rows and same column AFTER filtering

[leodm]

Database sample

Data de início real	Squad	Art
03/01/2022 17:30:00	Squad1	Art1
03/01/2022 22:00:43	Squad2	Art1
03/01/2022 22:30:00	Squad3	Art2
04/01/2022 09:00:00	Squad4	#N/A
04/01/2022 15:00:00	Squad1	Art1
04/01/2022 22:00:00	Squad5	Art2
05/01/2022 16:00:00	Squad6	Art3
05/01/2022 16:00:00	Squad1	Art1
05/01/2022 22:00:01	Squad5	Art2
05/01/2022 22:24:24	Squad2	Art1
05/01/2022 22:30:00	Squad5	Art2
05/01/2022 23:00:00	Squad3	Art2
05/01/2022 23:00:13	Squad3	Art2
06/01/2022 17:00:00	Squad7	Art3
06/01/2022 17:05:00	Squad7	Art3
06/01/2022 20:00:00	Squad5	Art2
06/01/2022 23:12:51	Squad2	Art1
07/01/2022 03:21:06	Squad2	Art1
07/01/2022 03:24:56	Squad2	Art1

Filter example (by selecting Squad1)

Data de início real	Squad	Art	Expected Result
03/01/2022 17:30:00	Squad1	Art1
04/01/2022 15:00:00	Squad1	Art1	21:30:00
05/01/2022 16:00:00	Squad1	Art1	25:00:00

Another filter example (by selecting Art1)

Data de início real	Squad	Art	Expected Result
03/01/2022 17:30:00	Squad1	Art1
03/01/2022 22:00:43	Squad2	Art1	4:30:43
04/01/2022 15:00:00	Squad1	Art1	16:59:17
05/01/2022 16:00:00	Squad1	Art1	25:00:00
05/01/2022 22:24:24	Squad2	Art1	6:24:24
06/01/2022 23:12:51	Squad2	Art1	24:48:27
07/01/2022 03:21:06	Squad2	Art1	4:08:15
07/01/2022 03:24:56	Squad2	Art1	0:03:50

I need to get each difference between the date rows like the "Expected Result" column and show them on visuals.

I believe this can only be achieved with a Measure, since a Calculated Column will show wrong results after filtering.

[FreemanZ · Accepted solution]

hi @leodm 

try to add plot all the columns with a measure like:

Result = 
VAR _date = MAX(TableName[Date])
VAR _datepre =
MAXX(
    FILTER(
        ALLSELECTED(TableName),
        TableName[Date]<_date
    ),
     TableName[Date]
)
VAR _Difference= _date - _datepre
VAR _Days = INT(_Difference)
VAR _Hours = HOUR(_Difference)
VAR _Minutes = MINUTE(_Difference)
VAR _Seconds = SECOND(_Difference)
VAR _DaysToHours = _Days * 24
VAR _TotalHours = _DaysToHours + _Hours
RETURN
IF(
    _datepre<>BLANK(),
    FORMAT(_TotalHours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds,"00"),
    " "
)

it worked like:

[leodm]

Hi @FreemanZ thanks for you answer,

I was able to get correct results with your provided Measure, awesome!

The only thing I couldn't do is show it in a graph. Is it because graphs can't display time like that? How could I use these results to show days & hours in graphs? Thank you.

[FreemanZ]

hi @leodm 

because the result of the measure is text, to plot graphically, try to return _Difference directly.

[leodm]

Hi @FreemanZ 

I tried outputting the Result in hours just to generate a graph:

The values are correct on the table, but for some reason the graph only shows the last value of the month.

I need one graph to show the AVERAGE and another one to show the TOTAL within a certain period. Could you help me with this one?

[BeaBF]

@leodm Hi!

Yes, you are correct. In order to calculate the time difference dynamically based on the filters, you will need to use a measure instead of a calculated column. Here's an example of how you can create a measure to calculate the time difference:

1. Open the Power BI desktop and select the table that contains the date and time values you want to calculate the time difference for.

2. Click on the "New measure" button in the "Modeling" tab.

3. In the formula bar, enter the following DAX formula:

Time Difference =
IF (
SELECTEDVALUE ( 'Table'[Squad] ) = BLANK () ||
SELECTEDVALUE ( 'Table'[Art] ) = BLANK (),
BLANK (),
CALCULATE (
SUM ( 'Table'[Data de início real] ) - MIN ( 'Table'[Data de início real] ),
ALLEXCEPT ( 'Table', 'Table'[Squad], 'Table'[Art] )
)
)

4. Add the measure to a visual to see the time difference between the selected Squad and Art values dynamically update based on the filters applied.

BBF

[leodm]

Hi @BeaBF , thanks for your answer,

Unfortunately I wasn't able to get the proper values with this Measure.