Average Number of Instances per week

[jlawrence_eplus]

I have a attendance report for our office where users have the option of coming into the office (vs working from home). Our management is interested in knowing how many people are coming in one day a week, vs two days, vs three days etc, with the ability to show how that has changed over each week. Maybe because it's Friday afternoon my brain is mush but I cannot figure out how to calculate this. 

Here's some sample Data:

Name	Office Attendance Date	End of Week
Person A -	9/14/2020	9/19/2020
Person A -	9/15/2020	9/19/2020
Person A -	9/18/2020	9/19/2020
Person B -	9/14/2020	9/19/2020
Person B -	9/17/2020	9/19/2020
Person C -	9/15/2020	9/19/2020
Person D -	9/15/2020	9/19/2020
Person D -	9/17/2020	9/19/2020
Person A -	9/21/2020	9/26/2020
Person A -	9/22/2020	9/26/2020
Person A -	9/23/2020	9/26/2020
Person B -	9/23/2020	9/26/2020
Person C -	9/22/2020	9/26/2020
Person C -	9/25/2020	9/26/2020
Person D -	9/24/2020	9/26/2020

And here's the results I'd like to show (based on the sample data):

Week 1	Count
1 Day	1
2 Days	2
3 Days	1
4 Days	0
5 Days	0
Week 2
1 Day	2
2 Days	1
3 Days	1
4 Days	0
5 Days	0

I do have a date dimension that has the end of the week in a column for every date but am unsure how to utilize this for the reporting. 

Any help would be greatly appreciated!

[stevedep · Accepted solution]

I guess this is what you are looking for (p.s. _week is a calculated column):

_NumberOfPeopleAmountOfDaysInOffice = 
COUNTROWS(
    FILTER(SUMMARIZE('Table','Table'[_Week],[Name], "No", DISTINCTCOUNT('Table'[Office Attendance Date])), [No] = SELECTEDVALUE(NumberOfDays[NumberOfDaysValue])
    ))

_Week = WEEKDAY('Table'[Office Attendance Date])

Helper table generated with:
(modeling -> add table)
NumberOfDays = GENERATESERIES(1,5,1)

As seen here, use cross tab to display change over time:

Link to file. 

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve. 

[shettysushma24]

Could you please help me understanding how did we got "NumberOfDays[NumberOfDaysValue]". I could find NumberOfDays[Value].

Also is matrix visual and cross tab both same? If yes could you please help me what should be mapped to columns rows and values?

Also I dint not understand where we have used the "_NumberOfPeopleAmountOfDaysInOffice" column.

[jlawrence_eplus]

Thanks that did it! Much appreciated!

[stevedep]

@jlawrence_eplus , Welcome! Anything for a thumbs up 😉

[daxer-almighty]

// Your calendar should have all the pieces
// of time that you'll need and connect
// it on [Date] to T[Office Attendance Date].
// T[End of Week] should be moved to your
// calendar. Also, you should have a table
// with all your people, 'Persons'. Then,
// these should be connected to T and T as 
// the fact table should be hidden (fact tables
// should always be hidden). There'll be
// another table in the model that will store
// number of days in a week, 1,2,3,4,5. It
// will be DISCONNECTED. The measure I give
// you works for any period of time, not
// only weeks. Let's say that Persons is
// joined to T on [PersonID] (an integer).
// All columns that store ID's in all tables 
// must be hidden as they are only keys
// without any business meaning.

[People Count] =
var __numOfDays =
    SELECTEDVALUE(
        // This is the disconnected table.
        'Office Working Days'[Number Of Days],
        -1 // this is important to get rid of 0's
    )
var __peopleCount =
    // We have to find out for each
    // visible person the number of
    // days from T and if the number
    // is equal to the above, we
    // have to add the person to the
    // set whose cardinality we'll
    // report.
    SUMX(
        People,
        CALCULATE(
            INT( COUNTROWS( T ) = __numOfDays )
        )
    )
return
    __peopleCount