Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative Biennial Budget

My issue is with the cumulative budget formula.  I have allocated my monthly biennial budget evenly accross 24 months.  When I use the cumulative formula below, the numbers aren't correct.

Cumulative Budget =
CALCULATE(
SUMX(SUMMARIZE(dCalendar,dCalendar[Date], "Budgets", 'PowerBI budget'[Budget Allocation]), [Budgets]),
FILTER(ALLSELECTED(dCalendar[Date]),
dCalendar[Date]<= MAX (dCalendar[Date])))
Below is shows the incorrect cumulative, along with what it should be. Thanks for any assistance, sorry the table is so crazy.
 
 
MonthinYearTotal Biennial BudgetBudgetAllocationCumulative Budget-incorrectCumulative Budget-Correct
Jan 2020 79,842,6203,326,7762,657,2483,326,776
Feb 2020 79,842,6203,326,7762,765,8936,653,552
Mar 2020 79,842,6203,326,7762,874,5379,980,328
Apr 2020 79,842,6203,326,7762,983,18113,307,104
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    This was solved on another forum, thanks for all the help.  It was needing two new calendar columns to group the biennial year and renumber each month in the biennium.  Then replacing the [month number] column with [bimonthnumber] in hnguy71  previously posted cumulative total.

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This was solved on another forum, thanks for all the help.  It was needing two new calendar columns to group the biennial year and renumber each month in the biennium.  Then replacing the [month number] column with [bimonthnumber] in hnguy71  previously posted cumulative total.

  • hnguy71's avatar
    hnguy71
    Super User
    6 years ago

    Anonymous 
    I think you can shorten it. As a measure:

     

    CumulativeBudget = TOTALYTD(SUM(YOUR_TABLE[BudgetAllocation]), dCalendar[Date], dCalendar[Date] <= MAX(dCalendar[Date]))

     


     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks so much for your response.

       

      My Budget Allocation column is a measure and I am not able to use the SUM with it. 

      I tried using SUMX instead but got this message:

      A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

       

      Also, this is for a biennial budget, will the TOTALYTD carry over the cumulative accross  to 2021?

      • hnguy71's avatar
        hnguy71
        Super User
        6 years ago

        hmm, 
        If you could supply a sample PBIX I can help you with your cumulative calculation (assuming there's no sensitive information such as employee information). In addition, if you don't add an end date, I believe it defaults to 12/31 of that same year, but in case it only evaluates only for current year, we can always make a measure to account for 2021 and beyond.

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Create a measure as below

    Cumulative Budget =
SUMX (
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] <= MAX ( 'Table'[date] ) ),
    [Budget Allocation]
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Unfortunately this didn't work for me.  My budget allocation column doesn't total at the end like yours.  Mine populates the same number through every month-year and the total. I'm going to try loading my pbix file.

      Thanks so much for your time.