Forum Discussion

Joshva's avatar
Joshva
New Member
2 years ago

Cumulative budget per year

I have a measure that takes all the different things I budgeted, and adds them as a single budget. 

My goal is to have the budget plotted cumulatively over the year (e.g. the budget for this year is 40k, I want to have a line from 0 at januari 1 to 40k at december 31).

 

Right now I have the following measure that tries to make a summary table with 1/365 of the budget, with a YTD function to add it together (it does not work):

Cumulative budget =
var dailybud = DIVIDE([budget], 365)
VAR budget_per_dag =
    SUMMARIZE(
        Date,
        Date[Year],
        Date[Month],
        Date[Day],
        "daily budget",
        TOTALYTD(dailybud,
        Date[Date])
)
RETURN SUMX(budget_per_dag, [daily budget])
 
However, it does not give me a value for each day, just the 1 day it was budgeted. So therefore I can not plot it.
The goal is to have it in this plot, replacing the current yellow line. Blue is costs, yellow is total budget.
 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Joshva 

     

    Thank you very much amitchandak for your prompt reply.

     

    It is recommended that you use the DATESYTD function to accumulate a daily budget for a year

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    “Table”

     

    “Date”

     

    Create a measure.

     

    Cumulative Budget = 
VAR dailyBudget = DIVIDE(SUM('Table'[budget]), 365)
RETURN 
    CALCULATE(
        SUMX(
            'Date',
            dailyBudget
        ),
        DATESYTD('Date'[Date])
    )

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Joshva's avatar
      Joshva
      New Member
      2 years ago

      This has gotten me closer, but I dont fully have it yet. My version of 'Table' has the budget for multiple years. How do I make sure it filters the year properly? right now, some budget is being assigned to the wrong year.

      Also, my cumulative budget (light green) is going up and down like this, and I don't quite know why.