Forum Discussion
Calculating days in specific location for devices
I am trying to calculate the days a device spent in a specific location. I figured it out in excel while venting my data. But I cannot figure out how to translate it over to Power BI for my reports.
The excel formula is used in "Days in L46" is
[ =IF(C2=C3,IF(F2="L46",DAYS(E2,E3),"0"),"0") ].
This gives me the days between transactions into, while in, and out of the specific location.
The next step would be to get a sum of the days for each device. And the formula I used for this in "Total Days in L46" is
[ =SUMIF(B:B,B2,I:I) ]
| TRANSACTION_ID | SERIAL_NUMBER | C_ATTRIBUTE1 | RCV_DATE | TRANSACTION_DATE | SUBINVENTORY_CODE | LOCATOR_CODE | TIF | Days in L46 | Total Days in L46 |
| 3668930733 | 03 | 1067331 | 4/7/2024 | 10-SEP-24 | L19 | L19WIPCC.Q3.0 | 256 | 0 | 1 |
| 3668930732 | 03 | 1067331 | 4/7/2024 | 10-SEP-24 | L17 | L17EXCPHLD.0.0 | 256 | 0 | 1 |
| 3590947181 | 03 | 1067331 | 4/7/2024 | 17-JUL-24 | L17 | L17EXCPHLD.0.0 | 256 | 0 | 1 |
| 3590947180 | 03 | 1067331 | 4/7/2024 | 17-JUL-24 | L02 | L02ST.0.0 | 256 | 0 | 1 |
| 3539769613 | 03 | 1067331 | 4/7/2024 | 10-JUN-24 | L02 | L02ST.0.0 | 256 | 0 | 1 |
| 3539769612 | 03 | 1067331 | 4/7/2024 | 10-JUN-24 | L19 | L19WIPCC.Q1.0 | 256 | 0 | 1 |
| 3475379263 | 03 | 1067331 | 4/7/2024 | 19-APR-24 | L19 | L19WIPCC.Q1.0 | 256 | 0 | 1 |
| 3475379262 | 03 | 1067331 | 4/7/2024 | 19-APR-24 | L46 | L46HOLD.1.19 | 256 | 1 | 1 |
| 3474432147 | 03 | 1067331 | 4/7/2024 | 18-APR-24 | L46 | L46HOLD.1.19 | 256 | 0 | 1 |
| 3474432146 | 03 | 1067331 | 4/7/2024 | 18-APR-24 | L46 | L46HOLD.1.19 | 256 | 0 | 1 |
| 3474389161 | 03 | 1067331 | 4/7/2024 | 18-APR-24 | L46 | L46HOLD.1.19 | 256 | 0 | 1 |
| 3474389160 | 03 | 1067331 | 4/7/2024 | 18-APR-24 | L15 | L15MGMT.0.0 | 256 | 0 | 1 |
| 3460209230 | 03 | 1067331 | 4/7/2024 | 07-APR-24 | L15 | L15MGMT.0.0 | 256 | 0 | 1 |
| 3460209229 | 03 | 1067331 | 4/7/2024 | 07-APR-24 | L15 | L15MGMT.0.0 | 256 | 0 | 1 |
| 3460209095 | 03 | 1067331 | 4/7/2024 | 07-APR-24 | L15 | L15MGMT.0.0 | 256 | 0 | 1 |
Thanks for the reply from danextian and Kedar_Pande, please allow me to provide another insight.
Hi jayman ,
Add an index column.
Create the following calculated columns.Days in L46 = VAR currentIndex = 'Table'[Index] VAR nextC_ATTRIBUTE1 = MAXX ( FILTER ( 'Table', 'Table'[Index] = currentIndex + 1 ), 'Table'[C_ATTRIBUTE1] ) VAR nextTRANSATION_DATE = MAXX ( FILTER ( 'Table', 'Table'[Index] = currentIndex + 1 ), 'Table'[TRANSACTION_DATE] ) RETURN IF ( nextC_ATTRIBUTE1 = 'Table'[C_ATTRIBUTE1], IF ( 'Table'[SUBINVENTORY_CODE] = "L46", DATEDIFF ( nextTRANSATION_DATE, 'Table'[TRANSACTION_DATE], DAY ), 0 ), 0 )Total Days in L46 = SUMX ( FILTER ( 'Table', 'Table'[SERIAL_NUMBER] = EARLIER ( 'Table'[SERIAL_NUMBER] ) ), 'Table'[Days in L46] )
The final result is as follows.Please see the attahed pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- danextian
Super User
Your sample data seems to be missing a column that indicates the grouping of the records - company name, device id, etc - so the result of the calculated column below will not be compartmentalized.
Days = VAR PrevRow = FILTER ( 'Table', 'Table'[TRANSACTION_DATE] < EARLIER ( 'Table'[TRANSACTION_DATE] ) && 'Table'[SUBINVENTORY_CODE] = "L46" ) VAR PrevDate = MAXX ( PrevRow, [TRANSACTION_DATE] ) RETURN IF ( 'Table'[SUBINVENTORY_CODE] = "L46", DATEDIFF ( PrevDate, 'Table'[TRANSACTION_DATE], DAY ) ) + 0 Total Days = SUM ( 'Table'[Days] ) Days2 = --if chronological order is based on transaction id VAR PrevRow = FILTER ( 'Table', 'Table'[TRANSACTION_ID] < EARLIER ( 'Table'[TRANSACTION_ID] ) && 'Table'[SUBINVENTORY_CODE] = "L46" ) VAR PrevDate = MAXX ( PrevRow, [TRANSACTION_DATE] ) RETURN IF ( 'Table'[SUBINVENTORY_CODE] = "L46", DATEDIFF ( PrevDate, 'Table'[TRANSACTION_DATE], DAY ) ) + 0 Total Days2 = SUM ( 'Table'[Days2] )All the devices are from the Company and would not have a unique identifier. The Serial Number is the Devices ID where the C_Attribute1 is the Uniquer ID for the device in they system.
The cronologicly by Transaction ID worked for the sample data I was working with to find a solution. But when I imported all 600,000+ line of the full data Power BI was unable to handle the calulations.
Thank you for your help.
Thanks for the reply from danextian and Kedar_Pande, please allow me to provide another insight.
Hi jayman ,
Add an index column.
Create the following calculated columns.Days in L46 = VAR currentIndex = 'Table'[Index] VAR nextC_ATTRIBUTE1 = MAXX ( FILTER ( 'Table', 'Table'[Index] = currentIndex + 1 ), 'Table'[C_ATTRIBUTE1] ) VAR nextTRANSATION_DATE = MAXX ( FILTER ( 'Table', 'Table'[Index] = currentIndex + 1 ), 'Table'[TRANSACTION_DATE] ) RETURN IF ( nextC_ATTRIBUTE1 = 'Table'[C_ATTRIBUTE1], IF ( 'Table'[SUBINVENTORY_CODE] = "L46", DATEDIFF ( nextTRANSATION_DATE, 'Table'[TRANSACTION_DATE], DAY ), 0 ), 0 )Total Days in L46 = SUMX ( FILTER ( 'Table', 'Table'[SERIAL_NUMBER] = EARLIER ( 'Table'[SERIAL_NUMBER] ) ), 'Table'[Days in L46] )
The final result is as follows.Please see the attahed pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you for this solution. It worked perfectly for the sample date I had and the full data I had imported to my report.
- lbendlin
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - Kedar_Pande
Create a calculated column:
Days in L46 =
IF(
'Table'[SERIAL_NUMBER] = CALCULATE(
MAX('Table'[SERIAL_NUMBER]),
ALLEXCEPT('Table', 'Table'[TRANSACTION_ID])
) &&
'Table'[LOCATOR_CODE] = "L46",
DATEDIFF('Table'[TRANSACTION_DATE],
CALCULATE(
MIN('Table'[TRANSACTION_DATE]),
ALLEXCEPT('Table', 'Table'[SERIAL_NUMBER], 'Table'[LOCATOR_CODE])
),
DAY),
0
)Create a calculated column:
Total Days in L46 =
CALCULATE(
SUM('Table'[Days in L46]),
FILTER('Table', 'Table'[SERIAL_NUMBER] = EARLIER('Table'[SERIAL_NUMBER]))
)💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
