Cumulative SUM Problem

Asfaa5 — Mon, 02 Mar 2020 02:55:05 GMT

Hi Team,

I have Tables Sales with column(Contract Start Date, Contract End Date), Calendar Table for Date Measures. Im trying to bring cumulative contract on Monthly Basis, I created 4 Measures:

1. New Adds = CALCULATE(COUNTROWS('Sales'))

2. Policy End = CALCULATE(COUNTROWS('Sales'), USERELATIONSHIP(Sales[Contract_End_Date__c],'Calendar'[Date]))

3. Policy Remaining Count = [New Adds]-[Policy End]

4. Cumulative Active Count =

VAR a =

SUMMARIZE (

FILTER ( ALL ( 'Calendar'),'Calendar'[Date] <= MAX ('Calendar'[Date])),

'Calendar'[Month],

"b", [Policy Remaining Count]

)

RETURN

SUMX(a, [Policy Remaining Count] )

The Result as below,

Month	New Adds	Policy End	Cumulative Active Count
May-18	4		4
Jun-18	255		255
Jul-18	256		256
Aug-18	1243		1243
Sep-18	6326		6326
Oct-18	25001		25001
Nov-18	8902		8902
Dec-18	15156		15156
Jan-19	15983		15983
Feb-19	15247		15247
Mar-19	25361		25361
Apr-19	34938		34938
May-19	36601	4	36597
Jun-19	35997	218	35779
Jul-19	53519	216	53303
Aug-19	67089	1034	66055

But i need like this :

Month	New Adds	Policy End	Cumulative Active Count
May-18	4		4
Jun-18	255		259
Jul-18	256		515
Aug-18	1243		1758
Sep-18	6326		8084
Oct-18	25001		33085
Nov-18	8902		41987
Dec-18	15156		57143
Jan-19	15983		73126
Feb-19	15247		88373
Mar-19	25361		113734
Apr-19	34938		148672
May-19	36601	4	185269
Jun-19	35997	218	221048
Jul-19	53519	216	274351
Aug-19	67089	1034	340406

Please guide me on the DAX Measures, where i have done the mistake and what needs to be corrected.

Re: Cumulative SUM Problem

Greg_Deckler — Mon, 02 Mar 2020 03:41:25 GMT

Tough to be specific without sample source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, what if you replaced Policy Remaining Count in your Cumulative Active Count measure with New Adds?

Re: Cumulative SUM Problem

Asfaa5 — Mon, 02 Mar 2020 04:09:57 GMT

Hi, Please find the screenshot of some data.

If i change bring the New Adds instead of Policy Remaining Count, It doesnt deduct the Policy End Count.

topic Cumulative SUM Problem in DAX Commands and Tips

Cumulative SUM Problem

Re: Cumulative SUM Problem

Re: Cumulative SUM Problem