Forecast for running total

Question

how to make a running total for each month ?

In power bi, I have a table refering the image below contains 12 month per year that only show single month and actual column is value get from summary table

But I want to display the cumulative forecast like if slicer is selected to January it will show the cumulative actual for january value until december like below image (below table is illustrated in excel. Not going to be load in power bi)

But if slicer is selected to February it will show like below. Which mean January value will be the actual but february is the sum of Jan + feb then the cumulative of march until dec will be using the feb actual value (below table is illustrated in excel. Not going to be load in power bi)

Same goes for the rest of the month if it already has the actual value.

Below are the tables i have in power bi

dangar332 · Answer

Hi,&nbsp;Shuuuu&nbsp;make a seprate table of Month no from date tablenewtable = all(date[month no])&nbsp;and use it in a slicer and don't make relationship with your data model&nbsp;make a new column in actual spent tablecolumn =
var curr_month = selectedvalue(newtable[month no])
return
sumx(
    filter(
      CROSSJOIN(
          values(date[month no]),
          values(actual spent[actual forecast])
      ),
      date[month no]&lt;=curr_month
    ),
    actual spent[actual forecast]
)&nbsp;

shuuuu · Answer

Hi&nbsp;Dangar332&nbsp; its just showing one month total instead of increasing like image above for january

dangar332 · Answer

Hi, Shuuuu&nbsp;&nbsp;Try to add zero after sumx()+0Still not working then provide sample data&nbsp;

shuuuu · Answer

hi&nbsp;Dangar332,&nbsp;I change my solution. Right now it able to cumulative prefectly for january. But when select slicer february it only cumulative the february data. The concept is nearly correct. But I want the output of feb - march like below.&nbsp;For example,&nbsp;jan - only cumulative jan valuefeb - jan + feb + then march until dec is cumulative using feb value&nbsp;march - jan + feb + march + april until dec is cumulative using march valuesame goes to when select other month slicer (slicer have jan until dec)concept that I want when select february slicer&nbsp;right now in pbi, its appear like belowthe formula i usedForecast Spent = VAR CurrentMonth = MONTH(MAX('Calendar'[Date]))VAR Cumulative =&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; CurrentMonth = 1, // Start cumulative from January&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMX (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('Calendar'[MonthNo],'Calendar'[MonthShortName]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Calendar'[MonthNo] &lt;= MAX('Calendar'[MonthNo])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Summary[Actual Spent]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CurrentMonth &gt; 1, // Start cumulative from February onwards&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMX (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('Calendar'[MonthNo],'Calendar'[MonthShortName]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Calendar'[MonthNo] &lt;= MAX('Calendar'[MonthNo])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Summary[Actual Spent]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK() // Return blank for other months&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )RETURN&nbsp; &nbsp; Cumulativemy tables&nbsp;&nbsp;

Forum Discussion

Forecast for running total

4 Replies

Related content

Running Totals

Forecast total display

Running total

Waterfall Charts with running totals

Tableau to PowerBI Running Totals Issue