Cumulative count by Month for defined period of time

[JuliaGumina]

Hi I have checkd all options of Cumulative Count I have found there and none of them worked out.

I have a table of accounts that we work with. Some of them are ok and some of them are in the risk of leaving or were in it.

The Fields are:

Account Id

CreatedOn (the date when the account started to work)

ChangedToRiskOn (the date when it was moved to the status "In the risk of leaving")

Cleint Status (if he is an Active Client, inActive or Temporary Blocked)

AccountId 	        CreatedOn	ChangedToRiskOn	        ClientStatus
1	                01.01.2017	 03.03.2018	         Active
2	                02.02.2017	 12.11.2017	         Active
3	                03.03.2017	 09.08.2017	         inActive
4	                04.04.2017		                 TempBl
5	                02.02.2018	 08.10.2018	         Active
6	                03.04.2018	 12.09.2018	         Active
7	                04.05.2018		                 Active
8	                07.05.2018	 15.06.2018	         TempBl
9	                06.06.2018		                 inActive
10	                11.07.2018	  03.09.2018	         inActive
11	                03.10.2018		                 TempBl
12	                09.11.2018		                 Active
13	                11.12.2018	  25.12.2018	         Active
14	                13.12.2018		                 Active
15	                25.12.2018		                 Active

I want to make a GRAPH with Count and Cumulative Count by MONTH of the Active Clients  which are in the Risk of Leaving for the last 10 Months. That means that the date will change dynamically with the beginning of every new month.

So it should I am supposed to get 2 data rows:

Month	  Count 	   Cumulative Count
March	 	1	                  1
April		0                         1
May		0                         1
June	 	0                         1	 	
July	 	0                         1	        
August		0	                  1	
September	1	                  2
October	        1	                  3
November	0	                  3
December	1	                  4

The issue is when I try to do cumulative total, it takes all data, not for the selected 10 Months. So if there is any Active Client in Risk of Leaving in 2017, he will calculate it and so the 1st cumulative figure in the chosen date fram will not be 1 but will include the figures from 2017.

Another option I tried simply puts 4 in every row.

I tried to do it with Measure and Column, adding the Date Table and without.

It doesn't work.

Can you please help me with the solution?

Thank you!

[v-yulgu-msft · Accepted solution]

Hi @JuliaGumina,

New a calendar table. Select [MonthName] column, change its "Sort By column" from default to [MonthNo]. And establish a one to many relationship between 'DateDim' and 'ClientStatus Table'.

DateDim =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2018, 12, 31 ) ),
    "MonthNo", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMMM" )
)

Create measures:

count =
CALCULATE (
    COUNT ( 'ClientStatus Table'[AccountId] ),
    FILTER ( 'ClientStatus Table', 'ClientStatus Table'[ClientStatus] = "Active" )
)
    + 0

Cumulative Count =
SUMX (
    FILTER ( ALLSELECTED ( DateDim ), DateDim[MonthNo] <= MAX ( DateDim[MonthNo] ) ),
    [count]
)

Add [MonthName] from 'DateDim' and above two measures into table visual. Apply "Relative Date filtering" in "Visual level filters", as shown in below screenshot.

Best regards,

Yuliana Gu

[JuliaGumina]

Thank you. But if i state the particular first and last dates in Dim Date how can I use it after when I have new clients? In 2019 for instance?

The table is updating.

Thank you!

[v-yulgu-msft]

Hi  @JuliaGumina,

DateDim =
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( MIN ( 'ClientStatus Table'[ChangedToRiskOn] ) ), 1, 1 ),
        DATE ( YEAR ( MAX ( 'ClientStatus Table'[ChangedToRiskOn] ) ), 12, 31 )
    ),
    "MonthNo", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMMM" )
)

Best regards,

Yuliana Gu