Table with incidents closed and opened by rows

[brunoemr]

Hi,

I have an incident table with all records opened and closed on last 6 months, but i need to build a table as i show below, i need to show a resume of Open and Closed record on a selected period(filter), It's important to show a column with a hard-coded text, is there a way to achive this:

Regards,

BrunoR

[parry2k]

@brunoemr how does your raw data looks like? What kind of relationship you have in your model? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[brunoemr]

I have two tables.

A table of all incidents, each record have Opened and Closed period, number and other fields.
A Date Dimension Table, related with Incidents by Open Period column.

I need to produce a table that show count of records open and closed( in different rows) based on a Period Slicer(From date Table). Besides that i need to have a column showing the the same count on last month.

Incidents Table:

Number	Status	Open	Closed	Open Period	Closed Period
INC1234	Open	23/10/2019	NULL	Oct 2019	NULL
INC1235	Wip	05/11/2019	NULL	Nov 2019	NULL
INC1236	Closed	15/10/2019	18/10/2019	Oct 2019	Oct 2019
INC1237	Closed	25/11/2019	26/11/2019	Nov 2019	Nov 2019
INC1238	Closed	23/10/2019	29/10/2019	Oct 2019	Oct 2019

What I am expecting are results like:

Period	Statistics Reference	Count	Count Last Month
Nov 2019	Open Incidents	2	3
Nov 2019	Closed Incidents	1	2

Regards,

BrunoR.

[parry2k · Accepted solution]

@brunoemr solution is attached you can tweak it as per your need.

[brunoemr]

Thanks.