Forum Discussion
Cumulative total for my data model
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.
4 Replies
- amitchandak
Super User
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"))
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
