How to see how many incidents were open at the end of a month

[Tarzamps]

Hi there,

I have a data set of incidents each with an opened date and a closed date. They are all now closed in status.

some were closed the same month they were opened and some were not I.e. opened in January, closed in February.

I would like to be able to show for each month how many incidents remained open at the end of that month. I can't count status as their all now closed and when I count closed it doesn't recognise the difference between the open month and closed month.

is there a formula I can use to work this out please?

thanks,

Tarzamps

[Ashish_Mathur]

Hi,

Share data in a format the can be pasted in an MS Excel file.  Please also show the expected resut in a simple Table format.

[Tarzamps]

Hi thank you! I have tried to follow the steps but am not getting the expected results. I attached some pictures so you can see what I've done. Could you let me know where I've gone wrong? Thanks 😊 

this shows the table I created:

then I added a measure:

 

as you can see the table is only showing one number

 

[Tarzamps]

 

this is how it looks now however I'm not sure it's counting correctly.. I want to know how many incidents were open at the end of the month I.e. which ones were opened in January but the closed date is not January and so on and so forth for each month..

[v-yueyunzh-msft · Accepted solution]

Hi , @Tarzamps 

Thanks for your quick response! you want to " which ones were opened in January but the closed date is not January and so on and so forth for each month..".

You can update the measure to this:

Count = var _Cur_month = MAX('Date'[Date])
var _end_of_month  = EOMONTH(_Cur_month,0)
var _t = FILTER('Table' , YEAR('Table'[OpenedDate])=YEAR(_Cur_month) && MONTH('Table'[OpenedDate])=MONTH(_Cur_month) && OR( YEAR('Table'[ClosedDate])<>YEAR(_Cur_month), MONTH('Table'[ClosedDate])<>MONTH(_Cur_month)))
return
COUNTROWS(_t)

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft]

Hi ,  @

Thanks for your quick response and the screenshot you provide. I think the issue caused by the filed is selected by the "Sum".

You need to check the field on the visual and change the "Sum" to the "Don't summarize", like this:

 

If this method dose not solve the problem , can you share the sample .pbix file to us(without the sensitive data) by OneDrive Link to use so that we can help you better!

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

@Tarzamps

[v-yueyunzh-msft]

Hi , @Tarzamps 

According to your description, you want to get the "how many incidents were open at the end of a month".

Here are the steps you can refer to :
(1)This is my test data:

(2)We need to click "New Table" to create a dimension dare table:

Date = ADDCOLUMNS( 
CALENDAR(FIRSTDATE('Table'[OpenedDate]),LASTDATE('Table'[ClosedDate])),
"Year", YEAR ( [Date] ),
"Month", MONTH([Date]),
"Year_month", year([Date]) * 100 + MONTH([Date]))

And we do not need to create a realationship between two tables.

(3)Then we can create a measure:

Count = var _Cur_month = MAX('Date'[Date])
var _end_of_month  = EOMONTH(_Cur_month,0)
var _t = FILTER('Table','Table'[OpenedDate]<= _end_of_month && 'Table'[ClosedDate]>_end_of_month)
return
COUNTROWS(_t)

(4)Then we can put the fields on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly