Create a custom count of rows for each month

[srhsloan]

I'm trying to create a running total type graph, but the change from one month to the next is not a simple case of add on everything created that month.

I have data like this:

And I want to create the following table which I can then use for a graph:

So the rules for the count are:

- Something is existing from the the month it was created, and every month after.

- Something is closed from the month it was closed and every month after.

I've used the built in time intelligence quick measure to create a running total for these 2 so that's fine.

- Something is active in the month it is activated until the month before it is closed. So item #1 is counted as active in jan, feb and march, then in april it is counted as closed. 

This is the part I'm having trouble working out the best approach for as I'm quite new to power bi.

I have a calendar table with all the dates in that I need:

CalTable = CALENDAR(DATE(2017,1,1),DATE(2020,12,31))

And I've joined this to my main table on closed date (I've also joined it on activated date and created date to get the running totals for the other data).

My current thinking is to create a custom table that somehow iterates over each month in the calendar table, then works out the totals for each month and puts this in the new custom table. But that seems like it might be quite inefficient and I'm not entirely sure where to start.

Are there any other ways anyone could recommend please?

[v-yuta-msft]

Hi srhsloan,

You can create a table with only month exists, then you can try "pivot" from Query Editor-> Transform-> Pivot Column on each column based on ID. Then use "Unpivot Column" to remove blanks.

For more details about pivot and unpivot, please refer to: http://radacad.com/pivot-and-unpivot-with-power-bi

Regards,

Jimmy Tao