cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thenonexpert
Regular Visitor

Help with measures and logic with selected date

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. 

 

datamodell (1).jpg

 

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

NamehousingtypeidHousing Typehousingunitid
JPE004H030135BA377E-021E-ED11-B83D-000D3ABB716DUmøblert hybel05D49DD1-ED22-ED11-B83D-000D3A263474
HAS009H02036010FF2F-9395-ED11-AAD0-000D3A272DB0Umøblert hybel med eget badC65A2855-9395-ED11-AAD0-000D3A272DB0
eirik test enhet497455FB-9B89-EC11-93B0-6045BD8C3DA3Umøblert 1-roms tilrettelagt for rullestol6E6BDB81-95A3-ED11-AAD1-000D3A272DB0
SAN099H050575E1C2B9-EBA8-EC11-983F-6045BD88C8D0Umøblert 1-roms leilighet for single714A3392-F0D1-EC11-A7B5-000D3A496518
RES009H0106  8212737D-637F-EC11-8D21-000D3A4C3362
JBG042H0102186E09A4-7395-EC11-B400-000D3AAFA383Møblert hybel med eget bad7B70A354-7595-EC11-B400-000D3AAFA383
JBG042H0103186E09A4-7395-EC11-B400-000D3AAFA383Møblert hybel med eget bad93C6C229-8695-EC11-B400-000D3AAFA383
JBG042H0104186E09A4-7395-EC11-B400-000D3AAFA383Møblert hybel med eget bad4707F2A8-8695-EC11-B400-000D3AAFA383
SAN099H01052B43C76E-EEA8-EC11-983F-6045BD88C8D0Umøblert 2-roms leilighet107D76B8-741D-ED11-B83D-000D3ABB716D
JPE004H020335BA377E-021E-ED11-B83D-000D3ABB716DUmøblert hybelBBAE1140-221E-ED11-B83D-000D3ABB716D
MAR024H01024BCC42B2-79D7-EC11-A7B6-000D3ABC4D99Møblert 1-roms leilighet for single553272EE-79D7-EC11-A7B6-000D3ABC4D99
MAR024H01034BCC42B2-79D7-EC11-A7B6-000D3ABC4D99Møblert 1-roms leilighet for single42618800-7AD7-EC11-A7B6-000D3ABC4D99


Housingtype

housingtypeid Number of Housing UnitsishcMain Application Profilehousingtypecode
7795FBB6-CC22-ED11-B83D-000D3A24AADB1FalseCoupleARAF2U
42429295-CD22-ED11-B83D-000D3A24AADB1FalseSingleARAE1U
DD18BD9D-9195-ED11-AAD0-000D3A272DB00FalseSingleKRIE1US
6010FF2F-9395-ED11-AAD0-000D3A272DB00FalseSingleCARHU
6FADBD1E-338F-EC11-B400-000D3A2ADD3210FalseSingleSOGHM
11B49B3D-DC90-EC11-B400-000D3A2ADD320FalseSingleKRIHU
D158AF0E-DD90-EC11-B400-000D3A2ADD323FalseSingleKRIHMBE

 

Reservations

 reservationidhousingtypeidhousingunitidreservedfromdatereservedtodatereservationtypename
AF10BEE5-9923-ED11-B83D-000D3A24AADB6CA97E80-8F89-EC11-93B0-6045BD8C321Cnull8/24/2022 12:00:00 AM1/1/2024 12:00:00 AMHousing Offer
FF4D74BA-6524-ED11-B83D-000D3A24AADBEF7BDCE7-F59A-EC11-B400-000D3ABD82FAnull8/10/2022 12:00:00 AM6/26/2023 12:00:00 AMHousing Offer
1C721C31-6924-ED11-B83D-000D3A24AADB02E76E37-DD90-EC11-B400-000D3A2ADD32null8/10/2022 12:00:00 AM11/26/2023 12:00:00 AMHousing Offer
102634EC-1925-ED11-B83D-000D3A24AADB02E76E37-DD90-EC11-B400-000D3A2ADD32null8/11/2022 12:00:00 AM6/30/2023 12:00:00 AMHousing Offer
D7043ACA-CC22-ED11-B83D-000D3A2634746CA97E80-8F89-EC11-93B0-6045BD8C321Cnull8/23/2022 12:00:00 AM2/24/2023 12:00:00 AMResidence
7F744278-BB23-ED11-B83D-000D3A26347435BA377E-021E-ED11-B83D-000D3ABB716Dnull8/24/2022 12:00:00 AM8/31/2022 12:00:00 AMHousing Offer
DE19D46A-5F8B-ED11-81AC-000D3A272DB06FADBD1E-338F-EC11-B400-000D3A2ADD32004FEB8F-338F-EC11-B400-000D3A2ADD321/3/2023 12:00:00 AM9/30/2023 12:00:00 AMTenancy Contract
B0503E40-2AB1-ED11-83FF-000D3A272DB042429295-CD22-ED11-B83D-000D3A24AADBnull1/27/2023 12:00:00 AM6/30/2023 12:00:00 AMResidence


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

Best.

0 REPLIES 0

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors