Transform Table with group, count and pivot

[StoryofData]

I need to completely transform a table from the top to the bottom one

I was looking at grouping but it seems pretty limited and not efficient for what I need to do, can anyone help me please?

[Ashish_Mathur]

Hi,

I cannot understand how you arrived at the result table from the input table.  In an MS Excel file, share input data in a worksheet and in another one show the expected result with an explanation.  I will try to translate your logic/formulas to the DAX language.

[StoryofData]

Hi Ashish, thank you for questioning me, that made me think and I came up with a partial solve thanks to @amitchandak suggestion down below. I still need help though. 

My desired outcome is this chart, I am open to other suggestions on visial, the goal is to show # of cases opened&closed and how many of them were substantiated.

what I currently have is this:

two relationship calculations - cases opened and cases closed but I cannot figure out how to make my power bi stacked chart look like the one I created in excel.

My raw data looks like this:

[Ashish_Mathur]

Hi,

Share data in a format the can be pasted in an MS Excel file.

[StoryofData]

Hi Ashish, will this work? 

CaseNumber	DateOpened	DateClosed	Function	Outcome
L1-22	12/5/2022		HR
L1-23	12/5/2022	3/14/2023	HR	Substantiated
L1-24	12/5/2022	1/13/2023	HR
L1-25	12/5/2022	1/12/2023	HR
L1-19	9/6/2022	12/14/2022	HR
L1-20	9/23/2022	11/10/2022	HR	Substantiated
L1-21	9/29/2022	12/5/2022	HR
L1-30	9/29/2022	10/10/2022	Finance

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[StoryofData]

Thank you so much!

[Ashish_Mathur]

You are welcome.

[amitchandak]

@StoryofData , refer if the approach of this blog or attached file can help

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