Producing a total from a measure - Daily Occupancy

[FlankyPank2___]

Hi,

I've produced a chart plotting Occupancy (From wonderful support on here!).

The info comes from a spreadsheet called "Data" in a format like:

John Smith         Building 1    Start Date: 01/04/2023   End Date: 03/06/2023

Phil McCrackin   Building 2    Start Date: 26/10/2023   End Date: 04/12/2023

Robin Banks       Building 1    Start Date: 25/08/2023   End Date: 26/09/2023

I Inclued a new column inside "Data" called "Column" = 1

I also needed to produce a new date table called "Date23-24" with every single day of the year

and place a measure inside of "Data":

Total Value = SUMX(FILTER(Data,Data[Start Date]<=max('Date23-24'[Date])&&Data[End Date]>=max('Date23-24'[Date])), [Column])+0

This plots wonderfully on a chart.  It responds to a date slicer using "Date23-24".

My problem, is I want to count up the dates occupied and hopefully have it respond to my other slicers e.g. "Building" as well as the date slicer.

So I know, through using the measure, that building one had:

01/04/2023  4 people

02/04/2023  5 people

03/04/2023  4 people

How can I show in a table or on a card that when 01,02,03,April is selected the total is 12?  Is it even possible?

Thanks in advance - I have spend HOURS on this and can't get close.

Thanks again

[Anonymous · Accepted solution]

Thanks for the reply from @Greg_Deckler , please allow me to provide another insight: 
Hi  @FlankyPank2___ ,

Here are the steps you can follow：

1. You might consider joining the relationship between the two tables. when the slicer is selected. which affects Building. based on [Start Date].

2. Create measure.

Measure =
var _select=SELECTEDVALUE('Table'[Building])
var _table=
SELECTCOLUMNS('Date23-24',"Date",[Date23-24])
var _table2=
CROSSJOIN(
    'Table',_table)
return
COUNTX(
   FILTER(
    _table2,[Date]>=[Start Date]&&[Date]<=[End Date]&&[Building]=_select),[Name])

3. Result:

There is a question as to how the "total is 12" is calculated, because when I add 4+5+4 it is 13.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous · Accepted solution]

Thanks for the reply from @Greg_Deckler , please allow me to provide another insight: 
Hi  @FlankyPank2___ ,

Here are the steps you can follow：

1. You might consider joining the relationship between the two tables. when the slicer is selected. which affects Building. based on [Start Date].

2. Create measure.

Measure =
var _select=SELECTEDVALUE('Table'[Building])
var _table=
SELECTCOLUMNS('Date23-24',"Date",[Date23-24])
var _table2=
CROSSJOIN(
    'Table',_table)
return
COUNTX(
   FILTER(
    _table2,[Date]>=[Start Date]&&[Date]<=[End Date]&&[Building]=_select),[Name])

3. Result:

There is a question as to how the "total is 12" is calculated, because when I add 4+5+4 it is 13.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[FlankyPank2___ · Accepted solution]

Creating a relationship skewed my chart which plots daily occupancy. 

But, the measure seems to work without creating the relationship.  I've done some checking and it looks to be accurate but i'm going to check again when it's less Monday Morning!

Thank you so much for your time. (& yes my Math is rubbish, it was 13!)  Thanks again

[Greg_Deckler]

@FlankyPank2___ First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[FlankyPank2___]

Thanks for your reply.

I'm not sure this is quite what I'm after? I think its how my two tables are able to interact with each other?  But I will take time and look into your recommendations and see if it does apply.

Thanks

[Greg_Deckler]

@FlankyPank2___ I think you are correct. I believe you actually need this one:

Open Tickets - Microsoft Fabric Community

[FlankyPank2___]

Thank you for this.  The answer from v-yan seemed to work but this is also very useful and i'll be trying it out.  Thank you