How to find an average row count of one table for each date category in a related table using DAX?

[Anonymous]

Hi folks, 

I am attempting to create a measure that calculates average submissions per pay period where submissions are stored in one table and pay periods are stored in a related table. 

Here is the relationship between the two tables. It is a 1:Many on Date and Created Date.

Dates Table Sample:

Date	Pay Period
6/30/2019	6/23/2019 - 7/06/2019
7/1/2019	6/23/2019 - 7/06/2019
7/3/2019	6/23/2019 - 7/06/2019
7/14/2019	7/07/2019 - 7/20/2019
7/17/2019	7/07/2019 - 7/20/2019
7/19/2019	7/07/2019 - 7/20/2019
7/21/2019	7/21/2019 - 8/03/2019
7/24/2019	7/21/2019 - 8/03/2019

Service Desk Table Sample:

id	Created Date	Employee
1	6/23/2019	Smith, Bob
2	6/23/2019	Roberts, Laurie
3	6/24/2019	Samuel, Tyler
4	7/01/2019	Smith, Bob
5	7/05/2019	Roberts, Laurie
6	7/07/2019	Smith, Bob
7	7/09/2019	Collins, Gabi
8	7/18/2019	Collins, Gabi
9	7/19/2019	Samuel, Tyler
10	7/20/2019	Smith, Bob
11	7/25/2019	Roberts, Laurie
12	7/28/2019	Smith, Bob
13	8/01/2019	Samuel, Tyler

For this sample data, the measure would return 4.333333. Pay period 6/23/2019-7/06/2019 had 5 requests, 7/07/2019 - 7/20/2019 had 5 requests, and 7/21/2019 - 8/03/2019 had 3 requests. 13 total requests/3 total pay periods = 4.33333.

Been stuck on this one for a while and would appreciate any help you all can offer. 

Thanks

-Jacob

[v-lid-msft]

Hi @Anonymous ,

If date column in your date table have all the value in create column, the @ImkeF 's formula should work fine, but if the relation is not complete, such as some value did not in Dates table, for example, the sample table you have shared, we can use the following formula to meet your requirement:

AverageMeasure = 
AVERAGEX (
    ADDCOLUMNS (
        DISTINCT ( 'DatesTable'[Pay Period] ),
        "Start", DATEVALUE ( LEFT ( [Pay Period], FIND ( " - ", [Pay Period] ) ) ),
        "End", DATEVALUE (
            RIGHT ( [Pay Period], LEN ( [Pay Period] ) - FIND ( " - ", [Pay Period] ) - 2 )
        )
    ),
    COUNTROWS (
        FILTER (
            'ServiceDeskTable',
            AND ( [Created Date] >= [Start], [Created Date] <= [End] )
        )
    )
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft,

I apologize for the confusion my sample data caused. My date table does contain every date, but I excluded a lot of dates as I wanted to have a few pay periods in order to best exemplify what I was trying to achieve.  I accepted @ImkeF 's answer as it is simple and most likely applicable to others who are trying to perform similar operations. However, your answer is most correct given the sample data I provided.

Thank you for your help 🙂

[ImkeF · Accepted solution]

Hi @Anonymous ,

this measure should work: 

Average over Pay Periods = AVERAGEX(VALUES(Dates[Pay Period]), COUNTROWS(RELATEDTABLE('Service Desk')))