Forum Discussion
Forecast for running total
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 t...
hi Dangar332, 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.
For example,
jan - only cumulative jan value
feb - jan + feb + then march until dec is cumulative using feb value
march - jan + feb + march + april until dec is cumulative using march value
same goes to when select other month slicer (slicer have jan until dec)
concept that I want when select february slicer
right now in pbi, its appear like below
the formula i used
Forecast Spent =
VAR CurrentMonth = MONTH(MAX('Calendar'[Date]))
VAR Cumulative =
IF(
CurrentMonth = 1, // Start cumulative from January
CALCULATE (
SUMX (
FILTER (
ALLSELECTED('Calendar'[MonthNo],'Calendar'[MonthShortName]),
'Calendar'[MonthNo] <= MAX('Calendar'[MonthNo])
),
Summary[Actual Spent]
)
),
IF(
CurrentMonth > 1, // Start cumulative from February onwards
CALCULATE (
SUMX (
FILTER (
ALLSELECTED('Calendar'[MonthNo],'Calendar'[MonthShortName]),
'Calendar'[MonthNo] <= MAX('Calendar'[MonthNo])
),
Summary[Actual Spent]
)
),
BLANK() // Return blank for other months
)
)
RETURN
Cumulative
my tables
my tables