Forum Discussion
Difference between dates on different rows and same column AFTER filtering
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.
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:
6 Replies
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!
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:
-
Open the Power BI desktop and select the table that contains the date and time values you want to calculate the time difference for.
-
Click on the "New measure" button in the "Modeling" tab.
-
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] )
)
)-
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
-
