Quick measure to find the average divided by Date of Service?

[jonnyA]

I have a report that I am working on that has the following ...

• Shift Name
• Total # of Patients Per Shift

My report has DOS, so I can select dates of Service, Jan 2021 through July 2021, but I do not know how to create an averaged measure that would show me the average for those months selected?

For example, If I were to click on Dates of Servie Jan 2021 through July 2021 then how could i calculate an average for that tiome period?

[v-xiaotang · Accepted solution]

Hi @jonnyA 

factly, in your case, the highly recommended thing is to create an independent date table. 

because not having your sample file, so I create one. you can try this.

-

1. create date table, and put it in filter

calendar = CALENDAR(DATE(2021,1,1),DATE(2021,10,1))

2. create the measure, the measure returns average of your selected months

average =
VAR _selectedDate1 =
    CALCULATE ( MIN ( 'calendar'[Date] ), ALLSELECTED ( 'calendar' ) )
VAR _selectedDate2 =
    CALCULATE ( MAX ( 'calendar'[Date] ), ALLSELECTED ( 'calendar' ) )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Total # of Patients Per Shift] ),
        FILTER (
            'Table',
            'Table'[Date] <= _selectedDate2
                && 'Table'[Date] >= _selectedDate1
        )
    )
VAR _months =
    DATEDIFF ( _selectedDate1, _selectedDate2, MONTH )
RETURN
    DIVIDE ( _sum, _months )

result

if you need more help, please @ me.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@jonnyA , You need to create measure like

sumx(values(Table[Month Year]), calculate(sum(Table[Total # of Patients Per Shift])))

if needed create new column

Month Year = format([Date], "mmm-yyyy")