Forum Discussion

Frequent Visitor

2 years ago

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...

Frequent Visitor

2 years ago

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

Resident Rockstar

2 years ago

Try to add zero after sumx()+0

Still not working then provide sample data

Shuuuu
Frequent Visitor
2 years ago
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