Calculate the difference between a date and the last date on a different rows

[tWdex]

Hi, I want to know the time period between two dates

Here is the table:

id	zone	type	date hour	id + zone	Wrong column I Created	Column/measure I want
001	workshop	enter	dd:mm:yyyy 08:00:ss	001workshop
001	workshop	exit	dd:mm:yyyy 08:15:ss	001workshop	00:15:ss	00:15:ss
001	workshop	enter	dd:mm:yyyy 09:00:ss	001workshop
001	workshop	exit	dd:mm:yyyy 09:10:ss	001workshop	01:25:ss	00:10:ss
001	Office	enter	dd:mm:yyyy 09:30:ss	001Office
001	Office	exit	dd:mm:yyyy 09:45:ss	001Office	00:15:ss	00:15:ss
002	Office	enter	dd:mm:yyyy 08:00:ss	002Office
002	Office	exit	dd:mm:yyyy 09:00:ss	002Office	01:00:ss	01:00:ss
003	workshop	enter	dd:mm:yyyy 08:00:ss	003workshop
003	workshop	exit	dd:mm:yyyy 08:10:ss	003workshop	00:10:ss	00:10:ss

I created a key column "id + zone" to use as a key and following this answer I managed to create this column:

Wrong column I created = 
     VAR MyLastDate =
         minX( 
         FILTER('table',
                'table'[ID + Zone] = EARLIER('table'[ID + Zone])
                && EARLIER(table[type]) = "exit" 
                && table[type] = "enter" ),
         'table'[date hour]) 
return(IF(MyLastDate<>BLANK(),FORMAT('table'[date hour])- MyLastDate,"HH:MM:SS"))))

This is wrong because it's based on a table that have a unique identifier so it use minx to get first "date hour" of that identifier, It doesnt work in my case because "id + zone" isnt a unique identifier, so I need only to get the "date hour" of the last "type = enter"

I also created this measure but the result are just a reverse version of "Wrong Column I created", same problems.

Previous Date = 
VAR thisdate =
    MAX ( table[date Hour] )
VAR thisid  =
    SELECTEDVALUE( table[ID + zone])
VAR thistype =
    SELECTEDVALUE ( table[type])
VAR previousdate =
    CALCULATE (
        MAX ( table[date Hour] ),
        FILTER(ALLSELECTED ( 'table' ),
        table[ID + zone]=thisid && table[type]="Enter"
    ))
RETURN
IF (
        OR ( ISBLANK ( previousdate ), thistype <> "Exit" ),
        0,
        FORMAT(thisdate - previousdate,"HH:MM:SS")
    )

I would also like if the user wrongfully clock two exit instead of one only the first/last get counted in "Column/measure I want", I dont know if it's possible in DAX tho.

[ryan_mayu · Accepted solution]

@tWdex 

you can try this

Column = 
VAR last=maxx(FILTER('Table (4)','Table (4)'[id ]=EARLIER('Table (4)'[id ])&&'Table (4)'[zone]=EARLIER('Table (4)'[zone])&&'Table (4)'[date hour]<EARLIER('Table (4)'[date hour])),'Table (4)'[date hour])
return if('Table (4)'[type]="enter",BLANK(),if(ISBLANK(last),BLANK(),FORMAT('Table (4)'[date hour]-last,"HH:MM:SS")))

[v-xulin-mstf]

Hi @tWdex

Create columns as:

a = 
CALCULATE(
    MAX('Table'[date hour]),
    FILTER(
        'Table',
        'Table'[Index]=EARLIER('Table'[Index])
    )
)

b = 
CALCULATE(
    MAX('Table'[date hour]),
    FILTER(
        'Table',
        'Table'[Index]<EARLIER('Table'[Index])
    )
)

Create measure as:

Duration = 


var duration=
IF(
    MAX('Table'[type])="exit",
    DATEDIFF(MAX('Table'[b]),MAX('Table'[a]),SECOND),
    BLANK()
)
return
RIGHT ( "0" & INT ( duration / 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( ( duration - INT (duration / 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & MOD (duration, 3600 ), 2 )

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[tWdex]

I was having an hard time understanding why it worked o the pbix but not on my real project, I think because my fake data are pretty tidy

I managed to make it work thanks to the datediff

 Now there is problem #2, if a user do an entry, then he do exit and exit again, for some reason I am unable to post more complex answer because I get the html problem / Sorry, unable to complete the action you requested.

edit: ok to avoid the html error I just replied without previewing, here a table

id	date hour	id + zone	type	zone	Duration in seconds
1	1899-12-30 08:00:00	001workshop	enter	workshop
2	1899-12-30 08:00:00	002Office	enter	Office
3	1899-12-30 08:00:00	003workshop	enter	workshop
3	1899-12-30 08:10:00	003workshop	exit	workshop	600
1	1899-12-30 08:15:00	001workshop	exit	workshop	900
1	1899-12-30 09:00:00	001workshop	enter	workshop
2	1899-12-30 09:00:00	002Office	exit	Office	3600
1	1899-12-30 09:10:00	001workshop	exit	workshop	600
1	1899-12-30 09:15:00	001workshop	exit	workshop	900
1	1899-12-30 09:30:00	001Office	enter	Office
1	1899-12-30 09:45:00	001Office	exit	Office	900

here a link to the modified pbix (the link will stop working for internal policies after 1 day) https://phoenixhub-my.sharepoint.com/:u:/g/personal/ncattaneo_phoenix-spa_com/EZayZNLJEypEvI87I3TPOR...

[tWdex]

I fixed this by doing:

Only Last = 
        VAR MyLastDate =
        minx(
            FILTER('Table',
            'Table'[id + zone]=EARLIER('Table'[id + zone]) && EARLIER('Table'[type])="exit"&&'Table'[type]="exit" && EARLIER('Table'[last d])='Table'[last d]),IF(EARLIER('Table'[Duration in seconds])>'Table'[Duration in seconds],1,IF(EARLIER('Table'[Duration in seconds])<'Table'[Duration in seconds],0,2)))
            return(IF(MyLastDate=2|| MyLastDate=1,'Table'[Duration in seconds],0))