Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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