Forum Discussion

Asfaa5's avatar
Asfaa5
Frequent Visitor
6 years ago

Cumulative SUM Problem

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,
 
MonthNew AddsPolicy EndCumulative Active Count
May-184 4
Jun-18255 255
Jul-18256 256
Aug-181243 1243
Sep-186326 6326
Oct-1825001 25001
Nov-188902 8902
Dec-1815156 15156
Jan-1915983 15983
Feb-1915247 15247
Mar-1925361 25361
Apr-1934938 34938
May-1936601436597
Jun-193599721835779
Jul-195351921653303
Aug-1967089103466055
 
But i need like this :
 
MonthNew AddsPolicy EndCumulative Active Count
May-184 4
Jun-18255 259
Jul-18256 515
Aug-181243 1758
Sep-186326 8084
Oct-1825001 33085
Nov-188902 41987
Dec-1815156 57143
Jan-1915983 73126
Feb-1915247 88373
Mar-1925361 113734
Apr-1934938 148672
May-19366014185269
Jun-1935997218221048
Jul-1953519216274351
Aug-19670891034340406
 
Please guide me on the DAX Measures, where i have done the mistake and what needs to be corrected.
 
 
 
 

2 Replies