Thank you for your post. I have a similar situation. I need to calculate the occupancy days in a month (for more than 6 aged care facilities in one report). Some of residents left, some are still in house. Some residents have been in the house for more than 10 years. I need to be able to get the occupancy days based on a date slicer within the selected days. I am not looking to analyse the data more than two years prior. I need to be able to show residents names with occupancy days in a month or just the show the total occupancy days per facility in a card. Also, I need to be able to show the empty number of rooms on a certain day and to be able to drill through to show the exact number of empty rooms (example room 405).
I have a resident table, date table (which I am very confused which period to show as I have residents for more than 10 years in the house, but the analysis is for two years prior. Your formula is based on arrival date, so I have to have that date in a date table I assume…). I have a facility name and code table and a room inventory table.
In the residents table I have the following columns: resident ID, admission date, departure date, room number, current resident, departed resident, number of stay days (which is calculated from the day of admission until the day of departure or current day when the data is refreshed. In the room inventory table, I have the following columns: facility code, room number and number of beds in the room. I have a customer table (contains only the name of customers).
Kindly note that the customer names are not allocated to the room inventory, so I was trying to show what rooms are occupied on a certain day by adding a column to the room inventory table with the customer names .I wasn’t able to do it .
Hi @Victoria - Request to provide a sample PBIX file to look into the data available and provide a Solution. Also, try to divide requirements in parts as providing Solutions to all the requirements in one post may not be possible.
May be start with most priority ones and add others later.
Thank you so much for reaching out.Kindly see sample file attached . I got the answer I wanted, but I created a copy of the residents table and called it existing residents and kept just the current ones,and I used lookupvalue formula. I was just wondering if there is a better formula to use to not have to duplicate a table only with the current residents.So for example from the residents table to say: if resident status is current then lookupvalue…I tried as I saw something similar in one of Sam’s demos but didn’t work for me ( I am not so advanced …) .Also I have residents in the house for more than 10 years but my calendar table is from July 2021.How do I format the calendar to not have blanks ? Test for entreprise DNA.pbix (41.8 KB).
Many thanks for your help.
Regards,
While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!