topic Re: Create a table and chart for open and closed based on different conditions for past 5 months in DAX Commands and Tips

Create a table and chart for open and closed based on different conditions for past 5 months

Suhasini1 — Thu, 16 Mar 2023 03:41:16 GMT

Hi,

Thanks in advance

I have to create open and closed count based on different conditions (but not on status column) for past 5 months

Open condition: severity is critical or high and datecreated>5/1/2022 and dare created <10/31/2022 and automated close > 10/1/2022

Got the counts like

May 1

June 1

July 3

Aug 2

Sep 2

Oct 2 (so Oct count is 11)

Nov 18

Dec 16 ( based on logic)

I need to populate this as a table

Please give me some ideas

Re: Create a table and chart for open and closed based on different conditions for past 5 months

amitchandak — Thu, 16 Mar 2023 04:34:10 GMT

Suhasini1 , What date you have, How do we know open and closed

One is HR Way

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Another Inventory Way Running total of open -closed

example

Active Employees = calculate(distinctCOUNT(EmpData[ID]) , filter(EmpData, EmpData[Hire Date] <= max(Date[Date]) && [Activity] = "Hire" ))
- calculate(distinctCOUNT(EmpData[ID]) , filter(EmpData, EmpData[Hire Date] <= max(Date[Date]) && [Activity] = "Termination" ))

Re: Create a table and chart for open and closed based on different conditions for past 5 months

Suhasini1 — Sun, 19 Mar 2023 19:15:11 GMT

Thank upu for your reply

I need to build this chart and table (below chart in screenshot) based on dates and count

Please find the attached open and closed queries in the screenshot.

Open logic is different and closed logic is different

Open logic is like count between the below dates

5/1/22 to 10/31/22 (should show as oct count)

5/1/22 to 11/30/2022

Please suggest.