Need to sum the Cumulative Result using DAX

[likhithar]

Hello All,

I have Month Column, I have some value till 202103(prevyr),I'm doing cumulative sum.On the top of that ,i need to cumulative to the cumulative result .As attached in the snap(how to achieve 55,300).Till 11,950 I'm able to achieve(using DAX Measure)

[v-yalanwu-msft · Accepted solution]

Hi, @likhithar ;

I tried to restore your original structure table and model as follows:

Then creates a measure to calculate:

cumulative = 
var _sum=SUMMARIZE('Fact',[Month],"1", CALCULATE(SUM([Value]),FILTER(ALL('Fact'),[Month]<=MAX([Month]))))
return SUMX(_sum,[1])

The final output is shown below:

If not right ,please correct me. 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Value cumulate total fix: =
VAR newtable =
ADDCOLUMNS (
FILTER ( ALL ( Data ), Data[Month] <= MAX ( Data[Month] ) ),
"@cumulate", CALCULATE ( SUM ( Data[Value] ) )
)
VAR cumulateofall =
ADDCOLUMNS (
ALL ( Data ),
"@cumulateofall",
CALCULATE (
CALCULATE (
SUM ( Data[Value] ),
FILTER ( ALL ( Data ), Data[Month] <= MAX ( Data[Month] ) )
)
)
)
RETURN
IF (
HASONEVALUE ( Data[Month] ),
SUMX ( newtable, [@cumulate] ),
SUMX ( cumulateofall, [@cumulateofall] )
)

[likhithar]

@Jihwan_Kim 
My financial Year starts from April and ends on March

In my Fact Table ,MONTH is the time period,in reporting i'm using filters from DateTable which is FinYear and Month Name,If Connect DateTable and fact Table relationship will be Many-Many, to avoid that I created Calendar Bridge Table which is DISTINCT(DateTable[YYYYMM])
Relationship will be like
FactTable[Month]-Calendar[YYYYMM]
Calendar[YYYYMM]-DateTable[YYYYMM](Bidirectional Filtering)

[amitchandak]

@likhithar , You need have incremental month value 202103 is in sortable format at 1st then filter else

you need to create a rank

Rank = Rankx(filter(Table, Table[Month]<> 'Till 202103') ,[Month year], ,asc,dense)+1 

Then

new measure=

sumx(filter(allselected(Table), Table[Rank] <= Max(Table[Rank]) ), Table[Value])

new column =

sumx(filter((Table), Table[Rank] <= earlier(Table[Rank]) ), Table[Value])