Average number of cases per day with multiple date/time slicers

[Elsie14]

Hi All,

My data looks something like this:

CaseID      Date             Type                            Channel

1              1-4-2018       Complaint type A        Phone

2              13-4-2018     Signal                          Mail

3              15-4-2018     Request                       Mail

4              18-4-2018     Complaint type B        Other

Ofcourse the data is spread out over 7 years with 300.000+ rows. I have added columns which state what day each date is. What I want to do is as follows: I want to calculate the average number of cases per day per period which is selected in the slicer. So if for example 2018 is selected in the slicer, I only want the average number of cases per day in 2018. If 2018 and Q1 is selected in the slicer, I only want the average number of cases per day in Q1 of 2018, and so on. However I can't seem the get the right measure for this. Can anyone help me out with this?

Kind regards

[v-juanli-msft · Accepted solution]

Hi @Elsie14

As you said, i am confuzed about “I have added columns which state what day each date is”, what are these columns and how they effect the expected average?

Based on my understanding, " the average number of cases per day per period which is selected in the slicer" means :

sum of cases during the selected period/total numbers of the period

If it is in this case,

Create a date table which is related to your fact data table. In date table, add period columns like year, month, quarter, and count the number of days of each period. 

count days per quarter =
CALCULATE (
    COUNT ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[YEAR], 'Table'[QUARTER] )
)

Create measures like below:

Count case ID =
CALCULATE ( COUNTROWS ( Table2 ), ALLSELECTED ( 'Table2' ) )

daybetween = MAX('Table'[count days per quarter])

average = CALCULATE(DIVIDE([Count case ID],[daybetween]),ALLSELECTED('Table2'))

As a result, we will see

Note: make [CaseID] not blank in the Visual level filter, for we use the number of rows shown in the table to count for the sum total of cases.

Here is my pbix

Best Regards

Maggie