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

[Anonymous]

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!!

[amitchandak]

@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

[Anonymous]

@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.

[Anonymous · Accepted solution]

@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/1081...

Thanks!

[Anonymous]

@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!!

[Anonymous]

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

Thank you!