topic Re: How to calculate Complaint Status (New, Closed and WIP) for each month? in DAX Commands and Tips

How to calculate Complaint Status (New, Closed and WIP) for each month?

Anonymous — Mon, 17 Aug 2020 16:09:15 GMT

Hi Everyone,

I am new to Power BI and in need of calculating Status for each complaint Nbr.

Criteria:

New - Created in the current month (can have both open/Closed complaints)

WIP - Open Complaints created prior to current month (closed month is null)

Closed - Closed Complaints in that month (but not created in that month as it will come under New status)

Everything is based on the 1st day of new month.

For example

I need to calculate "Status" field for each month as below using DAX function.

Sample dataset for Jan 2019:

Complaint Nbr CreatedMonth ClosedMonth Status

C1 Jan 2019 Jan 2019 New

C2 Dec 2018 WIP

C3 Jan 2019 New

C4 Dec 2018 Jan 2019 Closed

Thanks in Advance!!

Re: How to calculate Complaint Status (New, Closed and WIP) for each month?

amitchandak — Mon, 17 Aug 2020 16:15:59 GMT

Anonymous , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: How to calculate Complaint Status (New, Closed and WIP) for each month?

Anonymous — Mon, 17 Aug 2020 18:52:52 GMT

amitchandak : Thanks for your reply.

I am following your post to implement certain logic in my dataset. However, is there anyway to count distinct value of Compliant Nbr? In my dataset, i have duplicate complaint Nbr based on other dimension.

For Current Employee(in your dataset), it is similar to my New Status except that it is fetching all rows because of Countx function.

But i need to derive distinct count of complaint Nbr.

Please let me know if this is possible. Thank you in advance!!

Re: How to calculate Complaint Status (New, Closed and WIP) for each month?

Anonymous — Mon, 17 Aug 2020 18:59:36 GMT

amitchandak : Sorry, i just figured how to find distinct count in filtered table and it works now.

Thank you!

Re: How to calculate Complaint Status (New, Closed and WIP) for each month?

Anonymous — Mon, 17 Aug 2020 19:23:36 GMT

amitchandak : Hi,

I am facing difficulty in finding a solution for Closed status. I tried using the below formula for the same. But it is not giving correct result as expected.

For Closed : Closed in the respective months but those complaints should not be created in the same month.

Closed = calculate(DISTINCTCOUNT(Data[Complaint Nbr]),filter(Data,Month(Data[Creation Date])<> month(Data[Closure Date])) ,not(ISBLANK(Data[Closure Date])))

Kindy help me on the same.

Re: How to calculate Complaint Status (New, Closed and WIP) for each month?

Anonymous — Tue, 25 Aug 2020 06:37:50 GMT

v-lid-msft : Refered your post and modified according to my case and it worked perfectly.

https://community.powerbi.com/t5/Desktop/Carrying-Over-Open-Case-Amount-from-Previous-Month/m-p/1081682

Thanks!