PowerBI Variable that divides Budget in how much you should have spent by now in this quarter

[CharlesMS]

Does anyone know what variables to use to get this ?

I have a Budget variable lets say TOTAL BUDGET is X

And we have current Quarter lets say Q1 and its like February 4th.

This means 35 days passed from total 90 days of Quarter 1.

I want to divide the budget so that I can say like we have to spend 35/90 * Total Budget X.

[eliasayy]

Hello @CharlesMS 

Is it possible to provide some sample data? As in create something similar but small to help you out?

[CharlesMS]

To find the total Budget for the current channel , year and quarter I used this dax:


TotalBuget =
VAR __Current_year = Year(Now())
VAR __Quarter = "Q" & QUARTER(TODAY())
VAR __Result =
Calculate(SUM('TABLE'[Budget]), FILTER( 'TABLE','TABLE'[Year] = __Current_year && 'TABLE'[Quarter]=__Quarter && 'TABLE'[Channel]= "X"))
RETURN
__Result

But now i need to find how much I should have spent up to now, if we divide the budget equally for all days in the Quarter, QTD.  Also called Pacing.

[v-yinliw-msft · Accepted solution]

Hi @CharlesMS ,

You can try this method:

I assume that TODAY IS 2020/2/4:

New a measure:

TotalBudget =
VAR _currD =
    DATE ( 2020, 2, 4 )
VAR _currY =
    YEAR ( _currD )
VAR _currQ =
    "Q" & QUARTER ( _currD )
VAR _QTD =
    SWITCH (
        TRUE (),
        MAX ( [Quarter] ) = "Q1", DATE ( _currY, 1, 1 ),
        MAX ( [Quarter] ) = "Q2", DATE ( _currY, 4, 1 ),
        MAX ( [Quarter] ) = "Q3", DATE ( _currY, 7, 1 ),
        MAX ( [Quarter] ) = "Q4", DATE ( _currY, 10, 1 )
    )
VAR _QTDtotal =
    SWITCH (
        TRUE (),
        MAX ( [Quarter] ) = "Q1", DATEDIFF ( _QTD, DATE ( _currY, 3, 31 ), DAY ),
        MAX ( [Quarter] ) = "Q2", DATEDIFF ( _QTD, DATE ( _currY, 6, 30 ), DAY ),
        MAX ( [Quarter] ) = "Q3", DATEDIFF ( _QTD, DATE ( _currY, 9, 30 ), DAY ),
        MAX ( [Quarter] ) = "Q4", DATEDIFF ( _QTD, DATE ( _currY, 12, 31 ), DAY )
    )
VAR _diff =
    DATEDIFF ( _QTD, _currD, DAY )
RETURN
    DIVIDE ( _diff, _QTDtotal )
        * CALCULATE (
            MAX ( 'Table'[Budget] ),
            FILTER (
                'Table',
                'Table'[Quarter] = _currQ
                    && 'Table'[YEAR] = _currY
                    && 'Table'[Channel] = "x"
            )
        )

The result is:

Hope this helps you. Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

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

[CharlesMS]

So let's say I have a table with the following columns:

YEAR Quarter Channel Budget Market
2020  Q1           x           10000     USA

2019     Q2         y           20000     UK

2013    Q3         z           30000       NL

From that budget i want to caclulate how much I should have spent in this Quarter to this current Date (Quarter to Date -  QTD) 

This means that if we are in Quarter 1 (Q1) and its February 4th the caclulation is :

Days passed in current Quarter in current Year DIVIDED BY Total Days in current Quarter in Current Year MULTIPLIED by Budget 

35/90 * 10k