Unable to get historical average of received complaints

[Ab_2023]

Hello,

I have a table which has product code, Compaint number (alpha numeric) and complaint received date. Challenge is to get 12 month historical average of complaints baring recent 5 months and standard deviation of complaints.

Product CodeComplaint No.Comp. Receipt Date

Product A	C-09396	2022/05/18
Product B	C-09393	2022/05/13
Product C	C-09390	2022/05/17
Product A	C-07998	2021/06/08
Product B	C-079606	2021/06/08
Product B	C-079606	2021/06/08
…	..	….

Thank you for your help in advace.

Regards,

PowerBi Enthusiat

[amitchandak · Accepted solution]

@Ab_2023 , Rolling month avg with help from date table

12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Count('Table'[Complaint No)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

You can also consider windows

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

For standard-deviation-in-power-bi
https://www.edureka.co/community/31365/to-calculate-the-standard-deviation-in-power-bi
https://www.youtube.com/watch?v=ekcrlfZyAhg
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK... 

[Ab_2023]

Thank you for your quick response, Its helpful indeed however I was thinking how to get average leaving recent 5 months. Example we are in Jan 2023, we should consider complaints received on Aug 2022 and before (12 months). Thanks