Forum Discussion

Shuuuu's avatar
Shuuuu
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...
Need Help
Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

Hi, Shuuuu 

make a seprate table of Month no from date table
newtable = all(date[month no])

 

and use it in a slicer and don't make relationship with your data model

 

make a new column in actual spent table

column =
var curr_month = selectedvalue(newtable[month no])
return
sumx(
    filter(
      CROSSJOIN(
          values(date[month no]),
          values(actual spent[actual forecast])
      ),
      date[month no]<=curr_month
    ),
    actual spent[actual forecast]
)

 

Shuuuu's avatar
Shuuuu
Frequent Visitor
2 years ago

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

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    Hi, Shuuuu 

     

    Try to add zero after sumx()+0

    Still not working then provide sample data 

    • Shuuuu's avatar
      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