Create a measure based on two other measures with filter dependencies

[goodhvnting]

Hello, 

I would say I am a proficient Power BI user but I still have a lot to learn. I have been stuck on this formula for a few days now and hope that someone may have an idea how to solve it. 

I have two measures in my report that calculate occupancy rate. The measures are both different because the datasets are not the same and so require additional calculations.

Measure 1: 

CALCULATE(DIVIDE([RAP Occupied Nights], [RAP Total Roooms Occupied]),

FILTER('RAP data 2009-2023',

'RAP data 2009-2023'[Month_Number] IN {6,7,8}))

Measure 2: 

IFERROR(SUM('SHOP data'[Site Occupied Count])/SUM('SHOP data'[Site Actual Available Count]),0)

In order to calculate occupancy for both Measure 1 and Measure 2 I have this formula:

Occupancy Measure = Measure 1 + Measure 2

This works fine when I have a YEAR filter. When someone selects years 2009-2023 I get the occupancy rates for Measure 1. When someone selects YEAR filter 2024 they get occupancy rates for Measure 2. Great. 

The problem I have is when there is NO FILTER selected for YEAR. The measures get added up together and we get percentages that are over 100%. I think what I really want it to do is is this: 

IF YEAR filter is between 2009-2023 = Measure 1

IF YEAR filter is >= 2024 = Measure 2

IF NO YEAR filter is selected = (Measure 1+ Measure 2)/2

I think I need some kind of VAR and RETURN statement but can't quite figure it out. Maybe I am not on the right track either... appreciate any help this community can provide. 

Thanks in advance! 

[goodhvnting · Accepted solution]

I think I figured out what I need to do but would appreciate anyones comments if there is a better way to achieve the same result. I ended up using a SWITCH measure based on SELECTED VALUE of my YEAR filter. The calculation at the end is my ELSE statement if no YEAR filter is selected. 

This is what I ended up coming up with: 

Occupancy Measure = 

SWITCH(
SELECTEDVALUE(DateTbl[Year]),
"2024", [Measure 2],
"2023", [Measure 1],
"2022", [Measure 1],
"2021", [Measure 1],
"2020", [Measure 1],
"2019", [Measure 1],
"2018", [Measure 1],
"2017", [Measure 1],
"2016", [Measure 1],
"2015", [Measure 1],
"2014", [Measure 1],
"2013", [Measure 1],
"2012", [Measure 1],
"2011", [Measure 1],
"2010", [Measure 1],
"2009", [Measure 1],
DIVIDE([Measure 2]+[Measure 1],2)
)

[Anonymous]

Hi @goodhvnting ,

Glad to hear that you have solved your problem, please accept your answer as solution first so that more users facing the same problem can find the solution faster.
If someone else provides a more convenient answer in the future, please mark it as solution as well, thanks!

Best Regards,
Dino Tao