Calculating difference between daily revenue and budget with Power BI (DirectQuery)

Question

Using Direct Query I am trying to calculate the difference between budgeted and actual revenue per day, working with a monthly budget that is recorded on the first day of each month.

We have managed to calculate the size of the budget per day for each month, dividing the monthly budget by the number of working days that month. Due to the limitations of Direct Query we use a slicer as an input where one can choose the correct number of working days from a list.

We want to be able to subtract the calculated budget per day from the actual revenue that day. Working with data from Entity Store using DirectQuery, our possibilities are limited and we can not add new tables.

Any ideas on how to do this?

Here is a sample of what we have been able to make so far:

Sample table

We are using the following tables:

Revenue table

Budget table

#OfWorkDays table

We are also using a standard date table.

Here is the link to a full description of the problem including suggested solutions:

https://community.powerbi.com/t5/Desktop/Calculating-deviation-between-revenue-and-daily-budget-with/m-p/560861/highlight/true#M264794

Thanks, Carina

You need to put some examples with sample data. Read the community standards for question posting. — Rahul Neekhra
This doesn't address your main question, but why don't you use a column on your date table to indicate which days are working days to remove the need for the slicer with the number of working days. Your calculated budget per day would then need to count the number of working days in the month. This has the advantage, though, that you can get it to show zero budget on non-working days. — Gordon K
The post is now updated with sample data. That is a good idea, but as working days and holidays move from year to year (and our date table consists of several thousand rows) it is not that easy to do. — Carina

Alexis Olson Alexis Olson · Accepted Answer · 2018-11-12T17:07:10

Try using this for your daily budget measure:

Daily Budget =
DIVIDE(
    CALCULATE(
        SUM(Revenue[Actual Revenue]),
        STARTOFMONTH(Revenue[Date])
    ),
    SELECTEDVALUE(NumberOfWorkDays[NumberOfWorkDays], 1)
)

Then your deviation measure can simply be:

Deviation = SUM(Revenue[Actual Revenue]) - [Daily Budget]

Calculating difference between daily revenue and budget with Power BI (DirectQuery)

1 Answers