Help with measures and logic with selected date

[Anonymous]

Hi Power BI Community!

I am working in a case for counting available housingunits for a studentvillage and I could really use your help.

What I would like to create is a measure counting the available housingunits categorized by housingtype for a specific date selected in a date slicer.

We have 3 tables: reservations, housingunits & housingtype as shown in the diagram below. Reservations are a DireactQuery table whilst the others are in import mode. 

So what I initially have done is to create a measure counting total housingunits and categorize them by housingtype.

Count of all units =

CALCULATE(DISTINCTCOUNT(housingunit[Housing Unit]),

GROUPBY(housingtype,housingtype[housingtypecode]),

USERELATIONSHIP(housingtype[housingtypeid], housingunit[housingtypeid]))

To calculate which ones are reserved we use the reservations table. A reservation is always created on a housingtype, but not always on a specific housingunit, which is why we can only get the count on housingtype level. For a housingunit to be available it needs to be available from the selected date in the date slicer +365 days.

Snippets of the tables with data follow below:

Housingunits

Name	housingtypeid	Housing Type	housingunitid
JPE004H0301	35BA377E-021E-ED11-B83D-000D3ABB716D	Umøblert hybel	05D49DD1-ED22-ED11-B83D-000D3A263474
HAS009H0203	6010FF2F-9395-ED11-AAD0-000D3A272DB0	Umøblert hybel med eget bad	C65A2855-9395-ED11-AAD0-000D3A272DB0
eirik test enhet	497455FB-9B89-EC11-93B0-6045BD8C3DA3	Umøblert 1-roms tilrettelagt for rullestol	6E6BDB81-95A3-ED11-AAD1-000D3A272DB0
SAN099H0505	75E1C2B9-EBA8-EC11-983F-6045BD88C8D0	Umøblert 1-roms leilighet for single	714A3392-F0D1-EC11-A7B5-000D3A496518
RES009H0106			8212737D-637F-EC11-8D21-000D3A4C3362
JBG042H0102	186E09A4-7395-EC11-B400-000D3AAFA383	Møblert hybel med eget bad	7B70A354-7595-EC11-B400-000D3AAFA383
JBG042H0103	186E09A4-7395-EC11-B400-000D3AAFA383	Møblert hybel med eget bad	93C6C229-8695-EC11-B400-000D3AAFA383
JBG042H0104	186E09A4-7395-EC11-B400-000D3AAFA383	Møblert hybel med eget bad	4707F2A8-8695-EC11-B400-000D3AAFA383
SAN099H0105	2B43C76E-EEA8-EC11-983F-6045BD88C8D0	Umøblert 2-roms leilighet	107D76B8-741D-ED11-B83D-000D3ABB716D
JPE004H0203	35BA377E-021E-ED11-B83D-000D3ABB716D	Umøblert hybel	BBAE1140-221E-ED11-B83D-000D3ABB716D
MAR024H0102	4BCC42B2-79D7-EC11-A7B6-000D3ABC4D99	Møblert 1-roms leilighet for single	553272EE-79D7-EC11-A7B6-000D3ABC4D99
MAR024H0103	4BCC42B2-79D7-EC11-A7B6-000D3ABC4D99	Møblert 1-roms leilighet for single	42618800-7AD7-EC11-A7B6-000D3ABC4D99

Housingtype

housingtypeid	Number of Housing Units	ishc	Main Application Profile	housingtypecode
7795FBB6-CC22-ED11-B83D-000D3A24AADB	1	False	Couple	ARAF2U
42429295-CD22-ED11-B83D-000D3A24AADB	1	False	Single	ARAE1U
DD18BD9D-9195-ED11-AAD0-000D3A272DB0	0	False	Single	KRIE1US
6010FF2F-9395-ED11-AAD0-000D3A272DB0	0	False	Single	CARHU
6FADBD1E-338F-EC11-B400-000D3A2ADD32	10	False	Single	SOGHM
11B49B3D-DC90-EC11-B400-000D3A2ADD32	0	False	Single	KRIHU
D158AF0E-DD90-EC11-B400-000D3A2ADD32	3	False	Single	KRIHMBE

Reservations

reservationid	housingtypeid	housingunitid	reservedfromdate	reservedtodate	reservationtypename
AF10BEE5-9923-ED11-B83D-000D3A24AADB	6CA97E80-8F89-EC11-93B0-6045BD8C321C	null	8/24/2022 12:00:00 AM	1/1/2024 12:00:00 AM	Housing Offer
FF4D74BA-6524-ED11-B83D-000D3A24AADB	EF7BDCE7-F59A-EC11-B400-000D3ABD82FA	null	8/10/2022 12:00:00 AM	6/26/2023 12:00:00 AM	Housing Offer
1C721C31-6924-ED11-B83D-000D3A24AADB	02E76E37-DD90-EC11-B400-000D3A2ADD32	null	8/10/2022 12:00:00 AM	11/26/2023 12:00:00 AM	Housing Offer
102634EC-1925-ED11-B83D-000D3A24AADB	02E76E37-DD90-EC11-B400-000D3A2ADD32	null	8/11/2022 12:00:00 AM	6/30/2023 12:00:00 AM	Housing Offer
D7043ACA-CC22-ED11-B83D-000D3A263474	6CA97E80-8F89-EC11-93B0-6045BD8C321C	null	8/23/2022 12:00:00 AM	2/24/2023 12:00:00 AM	Residence
7F744278-BB23-ED11-B83D-000D3A263474	35BA377E-021E-ED11-B83D-000D3ABB716D	null	8/24/2022 12:00:00 AM	8/31/2022 12:00:00 AM	Housing Offer
DE19D46A-5F8B-ED11-81AC-000D3A272DB0	6FADBD1E-338F-EC11-B400-000D3A2ADD32	004FEB8F-338F-EC11-B400-000D3A2ADD32	1/3/2023 12:00:00 AM	9/30/2023 12:00:00 AM	Tenancy Contract
B0503E40-2AB1-ED11-83FF-000D3A272DB0	42429295-CD22-ED11-B83D-000D3A24AADB	null	1/27/2023 12:00:00 AM	6/30/2023 12:00:00 AM	Residence

Any suggestions on how to resolve this is highly appreciated. And if more information is needed, please reach out.

Best.