Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average occupancy by location, between two dates

Hi there,  I have a need to calculate the average occupancy of event locations for each event. I've tried varying combinations of 'CALCULATE', 'FILTER', 'DATESBETWEEN' and have spent a few hours tr...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

     

    Avg Occupancy CC =
    AVERAGEX (
    FILTER (
    TableA,
    TableA[Location] = TableB[Location]
    && TableA[Date] >= TableB[Start Date]
    && TableA[Date] <= TableB[End Date]
    ),
    TableA[Occupancy]
    )

     

     

    Link to the sample pbix file 

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

 

Avg Occupancy CC =
AVERAGEX (
FILTER (
TableA,
TableA[Location] = TableB[Location]
&& TableA[Date] >= TableB[Start Date]
&& TableA[Date] <= TableB[End Date]
),
TableA[Occupancy]
)

 

 

Link to the sample pbix file 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you so much!
    Seeing it laid out so clearly, it makes helps my understanding of 'FILTER' a lot.
    I had tried using 'AverageX' but clearly hadn't used the 'FILTER' correctly!


    Amazing to have a solution so quickly -