Calculating deviation between revenue and daily budget with Direct Query

[Anonymous]

Hi all,

We are working with Dynamics 365 for Finance and Operations, developing Power BI reports from Entity Store, and we have encountered an issue. Using Direct Query we are trying to calculate the deviation between the budgeted revenue and actual revenue per day. But the problem is that the monthly budget is only recorded on the first day of the month, so we cannot do the calculation directly.

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. Any ideas on how to do this?

Regards,

Carina

[v-qiuyu-msft]

Hi @Anonymous,

I'm not very clear about your requirement. Please add a table visual in the report, drag date, budgeted revenue and actual revenue fields to this visual, clarify your desired results. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Here is a visualization of what we have. The goal is to calculate the deviation between actuals and budget per day, but for that we need the daily budget calculated per day. But as it is now, we can only calculate the daily budget the first of each month.

[v-qiuyu-msft]

Hi @Anonymous,

Assume these columns in the table visual comes from the same table, you can create a measure below: 

Deviation = MAX('testdeviation'[Actual Revenue])-CALCULATE(SUM(testdeviation[Daily budget]),FILTER(ALL('testdeviation'),MONTH('testdeviation'[Dateval])=MONTH(MAX('testdeviation'[Dateval]))&& YEAR('testdeviation'[Dateval])=YEAR(MAX('testdeviation'[Dateval]))))

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft, 

The tables in the visual does not come from the same visual. In fact, they come from three different tables (actuals, budget and date table). In addition the daily budget is a calculated measure. Working with data from Entity Store using DirectQuery limits the possibilities of merging the columns to the same table, so even though your solution is good we will not be able to use it.

Best Regards,

Carina

[v-qiuyu-msft]

Hi @Anonymous,

Would you please share some sample data of these three tables, so I can try to modify the measure correspondingly? 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft

I cannot give you a sample of the real data, but here are the sample tables used in the example I sent you: 

 I haven't created a copy of the date table we use, but the only field I have used is the field which shows the date. The last table is what we use to calculate the daily budget. I just use it as a filter, and use that as an input in the DAX code for calculating the daily budget: 

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

Best Regards,

Carina

[v-qiuyu-msft]

Hi @Anonymous,

Based on your sample data, you can create a measure below: 

Deviation = CALCULATE(SUM(Budget[Budget]),FILTER(ALL('Revenue'),MONTH('Revenue'[Date])=MONTH(MAX('Revenue'[Date]))&& YEAR('Revenue'[Date])=YEAR(MAX('Revenue'[Date]))))-[Daily Budget]

By the way, the Daily Budget measure you provided should be: 

Daily Budget = DIVIDE(SUM(Budget[Budget]), SELECTEDVALUE(NumberOfWorkDays[NumberOfWorkDays], 1))

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft

I tried your solution, but it still doesn't work. The daily budget is still not being allocated to each day, and the deviation is not working. I think the problem with the daily budget allocation might be that since the only dates in that table are the first of each month, it will not work. We tried to merge and append with the date field from the date table, but that didn't work as the storage mode then changed to import mode. But thank you for helping!

Best regards,

Carina

[richbenmintz]

Hi @Anonymous,

I believe the screen shot belows provides what you need, the Budget Value Measure is defined as

Budget Value = 
 if(SUM(budget[Budget]) = blank() && SUM('revenue'[Actual Revenue]), 
        CALCULATE(
            divide(sum('budget'[Budget]),max(NumberOfWorkdays[NumberOfWorkDays])), FILTER(ALL('budget'), month('budget'[Date]) = MONTH(MIN('Date'[Date]))))
            ,divide(SUM(budget[Budget]),max(NumberOfWorkdays[NumberOfWorkDays])
        )
)

essentially, check to see if the Budget is Blank(), if it is replace with the allocated value for the selected month else use the simple allocation formula. For clarity I have just used the Max value from the disconnected slicer, you may choose to check for multiple values and replace with blank().

Hope this helps,

Richard