cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JuliaGumina Frequent Visitor
Frequent Visitor

Cumulative count by Month for defined period of time

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!

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: Cumulative count by Month for defined period of time

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" )
)

1.PNG2.PNG

 

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.

3.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
v-yulgu-msft Super Contributor
Super Contributor

Re: Cumulative count by Month for defined period of time

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" )
)

1.PNG2.PNG

 

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.

3.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JuliaGumina Frequent Visitor
Frequent Visitor

Re: Cumulative count by Month for defined period of time

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 Super Contributor
Super Contributor

Re: Cumulative count by Month for defined period of time

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 321 members 2,996 guests
Please welcome our newest community members: