Average per month Servicerequest

[mboon11111]

I have a table for servicerequest and would like an average per month based on number of Request ID's in that month.

RequestID	Created Time
1001	25/03/2020 16:00:24
1002	25/03/2020 16:05:38

[V-lianl-msft · Accepted solution]

Hi @mboon11111 ,

Please format created time as "year - month"

year_month = FORMAT(Sheet7[Created Time],"YYYY-MM")

Then try the DAX like below:

AVG_ALL_MONTH = 
VAR COUNT_ID = CALCULATE(COUNT(Sheet7[RequestID]),ALL(Sheet7))
VAR DISTIN_MONTH = CALCULATE(DISTINCTCOUNT(Sheet7[year_month]),ALL(Sheet7))
RETURN DIVIDE(COUNT_ID,DISTIN_MONTH)

Here is the sample pbix.

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

[amitchandak]

Create a date column

create date = [ Created Time].date

Join this with date table and have month year there and try a formula like

averagex(summarize(Date, date[Month Year],"_cnt",count(Table[RequestID])),[_cnt])