Forum Discussion

ExcelMonke's avatar
ExcelMonke
Impactful Individual
2 years ago
Solved

Count/Average Days Location is Open Per Week Per Month

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. 

Need Help
  • ExcelMonke's avatar
    ExcelMonke
    2 years ago

    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

4 Replies

  • Gabriele_hbto's avatar
    Gabriele_hbto
    Helper II
    2 years ago

    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's avatar
      ExcelMonke
      Impactful Individual
      2 years ago

      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's avatar
        Gabriele_hbto
        Helper II
        2 years ago

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

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual
    2 years ago

    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