Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Column with Time Since Entry

I have a records table, with each row having a unique date time.
The table contains information about people entering and exiting a state, as well as their status. 
How do I calculate a column that records the difference in time between entry and exit (days) at the time of exit?

 

Table:

DateTimeIDStatusEntryExit
25/10/2020 14:251001A  
29/10/2020 14:251001B10
1/12/2020 15:301002 10
5/02/2021 12:001001B01
15/04/2021 21:451002 

0

1
20/04/2021 21:451003A00


My expected result is:

DateTimeIDTime Since Entry
5/02/2021 12:00100199
15/04/2021 21:451003135


I want to be able to say - In April the average time since entry was 135 days.

I have a measure that calculates the Time Since Entry:

 

Time Since Entry = 
AVERAGEX(
    VALUES('Table'[ID]), 
        CALCULATE(
            DATEDIFF(
                MINX(FILTER('Table', 'Table'[Entry] =1), [DateTime]),
                MAXX(FILTER('Table', 'Table'[Exit] =1), [DateTime]),
                DAY)
        )
    )

 

However, the problem is that when I Filter this measure in my report by Month it excludes the datetime of entry when entry is not in April.


I therefore want to calculate a column, and tried the following DAX:

 

Time Since Entry = 
IF(
    'Table'[Exit] = 1,
    AVERAGEX(
        VALUES('Table'[ID]), 
            CALCULATE(
                DATEDIFF(
                    MINX(FILTER('Table', 'Table'[Entry] =1), [DateTime]),
                    MAXX(FILTER('Table', 'Table'[Exit] =1), [DateTime]),
                    DAY)
            )
    ),
    9999997
)

 


However this gives me:

DateTimeIDStatus_AEntryExitTime Since Entry
25/10/2020 14:251001A  99999997
29/10/2020 14:251001 1099999997
1/12/2020 15:301002 1099999997
5/02/2021 12:001001 01 
15/04/2021 21:451002 01 


First post:
https://community.powerbi.com/t5/Desktop/DATEDIFF-between-DateTime-Values-by-ID/m-p/1838306#M711595

Similar Issues:
https://community.powerbi.com/t5/Desktop/Calculating-time-difference/m-p/1528385#M628335
https://community.powerbi.com/t5/DAX-Commands-and-Tips/DATEDIFF-with-filter/m-p/769728
https://community.powerbi.com/t5/Desktop/using-datediff-with-filters/m-p/528938

 

  • Anonymous , Create a column like this and take an average of that

     

    if([Exit] =1, datediff(maxx(filter('Table', [ID] =earlier([ID]) && [Entry] =1 && [DateTime] <= earlier([DateTime])),[DateTime]),[DateTime], day), blank())

2 Replies

  • Anonymous , Create a column like this and take an average of that

     

    if([Exit] =1, datediff(maxx(filter('Table', [ID] =earlier([ID]) && [Entry] =1 && [DateTime] <= earlier([DateTime])),[DateTime]),[DateTime], day), blank())

  • Anonymous's avatar
    Anonymous
    Not applicable

    Thanks Mate!