Lookup previous date within table

[rbreneman]

Hi,

Thanks again @AlB for getting me this far. Just when I think I'm getting the hang of DAX I run up against a brick wall and can't see the path forward! I'm struggling to find the right words to describe what I'm trying to do, so hopefully the illustration below helps.

The table I have shows start and end dates for a resident and the specific bed and room IDs. I need to add a calculated column that gives in days the time it took to reoccupy the bed. I know I can use DATEDIFF to calculate the number of days between, but I'm struggling with how to lookup the previous EndDate in order to find the difference between that and StartDate. When there isn't a previous date I need to return a blank value.

I would appreciate any insight on how to accomplish this. Thanks so much!

[AlB · Accepted solution]

Hi @rbreneman 

If the bed ID is the column you want to identify the bed, then this will give you the previous end date:

=
CALCULATE (
    MAX ( Table1[EndDate] ),
    Table1[StartDate] < EARLIER ( Table1[StartDate] ),
    ALLEXCEPT ( Table1, Table1[BedID] )
)

 you can then use that in your datediff. Probably best would be to assign the value above to a VAR. You should also check for blanks, which will come up in cases as the last row, before applying the datediff

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[rbreneman]

Sorry for the delay in responding as I got pulled away onto other things. This was perfect! Worked exactly as I hoped. Thank you again so much, really appreciate your help! Have a great day!