Forum Discussion

mpmsltd's avatar
mpmsltd
Helper I
6 years ago
Solved

Daily Occupancy Average

Hi everybody,   I have a table that shows the Start Date, End Date and Customer ID. What I want to do is to see the daily occupancy average per period of time (year for example and be able to drill...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may download my solution file from here.

    Hope this helps.

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

Hi mpmsltd ,

 

Please check the following steps as below:

1. Create the inactive  relationship between tables as below.

 

2. Then we can create measures as below to work it out.

Measure = 
VAR mind =
    MIN ( 'date'[Date] )
VAR mad =
    MAX ( 'date'[Date] )
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            mind >= 'Table'[start date]
                && OR ( 'Table'[end date] >= mad, 'Table'[end date] = BLANK () )
        )
    )
RETURN
    a

Measure 2 = 
VAR d =
    MAX ( 'date'[Date] )
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        USERELATIONSHIP ( 'date'[Date], 'Table'[start date] )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        USERELATIONSHIP ( 'date'[Date], 'Table'[end date] )
    )
VAR c =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[start date] > d
                && OR ( 'Table'[end date] = BLANK (), 'Table'[end date] > d )
        )
    )
RETURN
    a - b + c

 

 

For more details, please check the pbix as attached.

 

mpmsltd's avatar
mpmsltd
Helper I
6 years ago

Hi v-frfei-msft thank you so much for your help.

But the totals are off, please check the file attached. For example the total for January 2017 is showing 586 but then all the days have more than 700 people. Can you please check it again? Also how can I attach my pbix file in here?

 THanks!