Budget on a daily and monthly level

[joshua1990]

Hello everyone!

I need to show the monthly budget and the accumulated budget until today in one matrix:

Area	Month To Date	Today Target
A1	5000	500
A2	750	750

I have the following measure to determine the weekly budget:

Budget = CALCULATE(SUM(Budget[Value]),FILTER(Budget,Budget[Target] = "Budget"))

 

For the MTD Target I use the following Measure:

Budget MTD = CALCULATE ( [Budget], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Fiscal Year] = VALUES ( 'Calendar'[Fiscal Year] ) && 'Calendar'[Fiscal Month Number] = VALUES ( 'Calendar'[Fiscal Month Number] ) && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) )

So, this measures shows me the monthly budget.

Now, I need to calculate the same for the accumulated budget until today.

Basically it should be something like: (Budget MTD / Sum Workingdays) * Workingdays Past

Does someone have any idea?

[Mariusz]

Hi @joshua1990 

If you need to work with budgets on different granularities then this post should help.

https://www.daxpatterns.com/budget-patterns/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[joshua1990]

Thanks a lot!

How would you edit this measure to get the budget on a daily basis?

[Budget] :=
IF (
    [IsBudgetValid],
    SUM ( Budget[BudgetAmount] ),
    SUMX (
        VALUES ( 'Date'[Year] ),
        CALCULATE (
            COUNTROWS ( 'Date' ),
            'Date'[WorkingDay] = TRUE
        )
            / CALCULATE (
                COUNTROWS ( 'Date' ),
                ALLEXCEPT ( 'Date', 'Date'[Year] ),
                'Date'[WorkingDay] = TRUE
            )
            * CALCULATE (
                SUM ( Budget[BudgetAmount] ),
                FILTER ( ALL ( Budget[Year] ), Budget[Year] = 'Date'[Year] )
            )
    )
)

Now it's based on a yearly bucket, right?

My Budget table has the following structure:

• Area
• Fiscal Year-Week
• Value