Forum Discussion

FlankyPank2___'s avatar
FlankyPank2___
Icon for Helper I rankHelper I
2 years ago
Solved

Producing a total from a measure - Daily Occupancy

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
 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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___'s avatar
    FlankyPank2___
    2 years ago

    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

     

     

6 Replies