Sum on month for item with ongoing period

[zockan]

Hi!

I have been struggling with how to summarize and display open and closed incidents over a period of time. I would like to show all incidents that on some point were active each month. Example: An incident was created in March and closed in May. I want that incident to be counted and displayed in March, April and May. Another incident was created in April and is still ongoing. I would like that incident to be counted (and displayed) in April, May and June. Is this possible without creating one row for each case and affected month in the data set?

I attach a picture of the example data to clarify.

Thanks!

Regards

Zoë

[Eric_Zhang]

@zockan

Generate a calendar table(DimDate) and create a calculated table. Suppose a sample data as below.

DimDate = FILTER(CALENDAR(MIN(Incidents[Start Date]),MAX(Incidents[End Date])),DAY([Date])=1)

CalculatedTable = FILTER(CROSSJOIN(Incidents,DimDate),DimDate[Date]>=Incidents[Start Date]&&DimDate[Date]<=Incidents[End Date])

And a measure calculate the row counts for each month.

rowCnt = COUNTROWS(CalculatedTable)

Display the calculated table in a stacked column chart.

See more details in the attached pbix.

If this approach works, please accept it as a solution:). For any question, feel free to let me know.

[Sean · Accepted solution]

@zockan It sounds like you are looking to calculate and plot the Active Count

The original solution can be found here

http://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-cam...

Me and @KHorseman recently tackled this couple different ways which you can see in this post

http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregat...

You will have to make some modifications because you say you have blank end dates for not closed incidents...

However I think these posts should help guide you! 

[zockan]

Thank you so much! I used the Holiday example. I appreciate your help!

[CheenuSing]

Hi Zockan,

The logic to be applied is finding out how many go closed in a month and how many got closed after the current month.

This is explained as formula in the link provided below or you could google  searching for  "Carryover values in dax".  This will show up the link below under "carry forward from previous months"  

Please check the link

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9125471c-8868-4109-a52c-cd4f74fb55eb/carry-...

Read through the section where it talks ablut creating measures like InitiatedCurrentMonth, InitiatedCompletedCurrentMonth,

IntitatedPriorCompletedSubsequent and the final formual TotalPending.  This should give you the result.

This assumes you have a calendar table in your application.

I tried this out and it works.   Let me know if you need further assistance.

Thanks for posing this question and it has helped me acquire new knowledge in using dax.

Cheers

CheenuSing

[Nhallquist]

Basially, you can't plot data you don't have, and in this case, all you have is Start/End Dates.   PowerBI won't plot dates in between, because it doesn't know them.  You have a couple of options. 

1.  You can find a way to creatively plot the durations between the Start/End dates.  Not sure this will work for you, but it is somethig to think about.

2.  You have to create the dates between the Start/End dates.  

I wish I could give you better advice.  Have you gone to the Custom Visual Library and tried using a Gantt chart or somthing?  That would be a last ditch effort, but I wouldn't pass that option by.  you might have to fill in the End Date with Today(), if one doesn't exist.

Good luck,

Nate