Calculating days in specific location for devices

[jayman]

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

[Anonymous · Accepted solution]

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.

[jayman]

Thank you for this solution. It worked perfectly for the sample date I had and the full data I had imported to my report.

[Kedar_Pande]

@jayman 

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

[danextian]

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] )

[jayman]

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.

[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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...