Count/Average Days Location is Open Per Week Per Month

[ExcelMonke]

Hello,
I am currently struggling with what seemingly should be a straightforward calculation. I am looking for a way to count the number of days a location is open per week per month. For example, Location X is open, on average, 2 days per week per month. 

I sort of have my days open per week calculation figured out (it currently only works if I have a Week Beginning column in the matrix) and uses the following measure:

'Measure Table'[TEST] = CALCULATE (
    DISTINCTCOUNT ( 'FactTable'[Date] ),
    'FactTable'[Status] = "Open",
    ALLEXCEPT ( 'Date Dim', 'Date Dim'[WeekBeginningDT] )
)

My next approach would be to calculate number of weeks per month, and divide the above measure by the weeks per month. Is there a better way to go about this? Right now, if I remove the WeekBeginningDT column from the Matrix, i am getting a result that is far off the correct value. 

[ExcelMonke · Accepted solution]

This may help others with the same problem as I found my solution:

VAR _DaysWeek = 
CALCULATE (
    COUNT ( 'Date Dim'[DayName] ),
    FILTER (
        'FactTable',
        'FactTable'[Status] = "Open" //Include only days that are open
    ),
    ALLEXCEPT ( 'Date Dim', 'Date Dim'[WeekBeginningDT] )
)

VAR _WeeksPerMonth = DISTINCTCOUNT('Date Dim'[WeekBeginningDT])

RETURN
ROUND((_DaysWeek/_WeeksPerMonth),0

[Gabriele_hbto]

I didn't understand if you want to count the open days or calculate a mean.

What you should do is just this measure CALCULATE ( DISTINCTCOUNT ( 'FactTable'[Date] ), 'FactTable'[Status] = "Open")

 then on the matrix you put the date in the rows, the location in the column and the measure in the value

so you get for each time span how many days was open

[ExcelMonke]

Thank you for the response! Great question. To expand, I want to calculate the average days per week a location is open, per month. The final result I am getting at is creating a distribution of average number of days a location is open across any given time frame.

[Gabriele_hbto]

then
DIVIDE(CALCULATE ( DISTINCTCOUNT ( 'FactTable'[Date] ), 'FactTable'[Status] = "Open", DISTINCTCOUNT ( 'FactTable'[Weeknum] ))