Forum Discussion

mboon11111's avatar
mboon11111
Frequent Visitor
6 years ago
Solved

Average per month Servicerequest

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 2...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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])