cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Lookup previous date within table

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!

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Helper II

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors