Forum Discussion

jayman's avatar
jayman
Regular Visitor
1 year ago
Solved

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. ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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's avatar
    jayman
    Regular Visitor
    1 year ago

    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.