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.

 

RequestIDCreated Time  
100125/03/2020 16:00:24  
100225/03/2020 16:05:38  
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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super 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])

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    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.