Cumulative total for my data model

[Anonymous]

Hello please help me to calculate cumulative based on this data model. In my data model there is no date sorting and most of the solutions on the internet are based on this. 

in my data model index corresponds to value month so for each month of an year we have value months which have data points. picture 1 shows graph based on this data model.

And picture 2 shows the desired result. 

So for the month may the graph shoud show april and may=april+may

similarly for december graph 2 must show till value month december

thank you in advance.

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Please refer the formula.

Measure = CALCULATE(SUM('Table'[value]),FILTER(ALLSELECTED('Table'),'Table'[index]<=MAX('Table'[index])))

Result would be shown as below.

Best Regards,

Jay

[amitchandak]

@Anonymous , Try

Create a new column

Date = "01-" & [Month] & "-" [year] //Change data type to date

and then measure

Cumm ACTUAL+FORECAST = CALCULATE(SUM(Table[Value]),filter(allselected(Table),Table[date] <=max(Table[Date]) && Table[Type] = "ACTUAL+FORECAST"))

Cumm BUDGET = CALCULATE(SUM(Table[Value]),filter(allselected(Table),Table[date] <=max(Table[Date]) && Table[Type] = "BUDGET"))

[Anonymous]

@amitchandak  the months on x axis are from value month column.

So from each month we have 12 value months, index represents value month in fiscal numbering.

so I need for every month there should be a cumulative total till that value month 

eg., for month apr chart 2 shows value month apr

       for month may chart 2 shows value month apr and apr+may

      for month jun  chart 2 shows value month apr , apr+may and apr+may+jun

I you get this data model please help me

[amitchandak]

@Anonymous , That is like YTD, Better create a date  table and use YTD

YTD = CALCULATE(SUM(Table[Value]),DATESYTD('Date'[Date],"3/31"), filter(Table, Table[Type] = "ACTUAL+FORECAST"))  //end of year is march