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.

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