Cumulative Monthly Report

[xrobc]

Hi

I have a range of data that looks something like the attached image. The data is from an external database that I have cleaned up in Power Query (it wouldn't clean up in Power Pivot) and brought into Excel.

I would like to produce a cumulative monthly report that total of requests that were open at a specific date (such as the 1st day of the month).

I could produce a separate table with my list of dates, but was hoping to generate this somehow from the date range so that I don't need to keep adding in extra dates. Is there some way that I can do this with DAX?

I already have the table added to my Power Pivot Data Model and have some other Power Pivot Tables in the workbook and was hoping to produce something similar.

Thanks

 

[alansidman]

Based upon the data provided, please mock up what your expected results should look like.

 

[xrobc]

Based upon the data provided, please mock up what your expected results should look like.

Thanks for the reply.

This is the sort of graph that I was hoping to end up with, just not sure how to get there.

 

[jdellasala]

Using this table:

Book1
	A	B	C	D	E
1	Request ID	Status	Date Raised	Date Closed	Date put on Hold
2	R1	Active	11/29/2018		01/31/2019
3	R2	Pending	10/29/2016
4	R3	Active	06/06/2016	08/31/2016
5	R4	Pending	10/26/2014
6	R5	Pending	12/07/2017		02/28/2018
7	R6	Pending	02/21/2016
8	R7	Pending	10/15/2015		12/31/2015
9	R8	Active	03/14/2018	05/31/2018
10	R9	Pending	01/23/2014		03/31/2014
11	R10	Pending	01/07/2019	03/31/2019
12	R11	Pending	05/16/2019		07/31/2019
13	R12	Active	02/03/2018
14	R13	Pending	03/11/2014		05/31/2014
15	R14	Active	03/01/2013
16	R15	Active	07/13/2012
17	R16	Active	10/05/2014
18	R17	Pending	01/31/2018	03/31/2018
19	R18	Pending	10/25/2012		12/31/2012
20	R19	Pending	02/05/2018	04/30/2018
21	R20	Active	02/01/2013
22	R21	Pending	08/26/2018
23	R22	Pending	10/29/2012	12/31/2012
24	R23	Active	08/17/2016	10/31/2016
25	R24	Pending	09/22/2014	11/30/2014
26	R25	Pending	03/15/2019
Sheet2

(Which took longer to do than the solution! PLEASE use XL2BB when posting data!)
I added this to the table in PQ:

= Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Date Raised]), type date)

and loaded it to a Pivot Table/Chart:

Book1
	G	H
4	Month Start	Count
5	07/01/2012	1
6	10/01/2012	2
7	02/01/2013	1
8	03/01/2013	1
9	01/01/2014	1
10	03/01/2014	1
11	09/01/2014	1
12	10/01/2014	2
13	10/01/2015	1
14	02/01/2016	1
15	06/01/2016	1
16	08/01/2016	1
17	10/01/2016	1
18	12/01/2017	1
19	01/01/2018	1
20	02/01/2018	2
21	03/01/2018	1
22	08/01/2018	1
23	11/01/2018	1
24	01/01/2019	1
25	03/01/2019	1
26	05/01/2019	1
27	Grand Total	25
Sheet2

Hope that helps!

 

[xrobc]

Thanks.

I appear to be missing something here......

To which table do I add the Column? My table of data or my table of month start dates?

Neither seem to work for me?

Also, I was looking for a cumulative total of the number that we Active on a given date. Doesn't this give me the total raised in a month?

 

[jdellasala]

Thanks.

I appear to be missing something here......

To which table do I add the Column? My table of data or my table of month start dates?

Neither seem to work for me?

Also, I was looking for a cumulative total of the number that we Active on a given date. Doesn't this give me the total raised in a month?

You add the M Code I posted to the last line of your current query:

= Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Date Raised]), type date)

From PQ to get a chart you have to have a Pivot Table to base the chart on which is included above.

 

[xrobc]

You add the M Code I posted to the last line of your current query:

= Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Date Raised]), type date)

From PQ to get a chart you have to have a Pivot Table to base the chart on which is included above.

Thanks, I think I got it working.

However, it's giving me a count of raised each start of the month.

I was hoping to get a total of the number that are "Active" at the start of each month.

 

[jdellasala]

Thanks.

I appear to be missing something here......

To which table do I add the Column? My table of data or my table of month start dates?

Neither seem to work for me?

Also, I was looking for a cumulative total of the number that we Active on a given date. Doesn't this give me the total raised in a month?

Remove all but Active in the query before the line I provided.