cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carinam Frequent Visitor
Frequent Visitor

Calculating deviation between revenue and daily budget with Direct Query

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

9 REPLIES 9
Moderator v-qiuyu-msft
Moderator

Re: Calculating deviation between revenue and daily budget with Direct Query

Hi @carinam,

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
carinam Frequent Visitor
Frequent Visitor

Re: Calculating deviation between revenue and daily budget with Direct Query

Skjermbilde.PNG

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.

Moderator v-qiuyu-msft
Moderator

Re: Calculating deviation between revenue and daily budget with Direct Query

Hi @carinam,

 

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]))))

 

q4.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
carinam Frequent Visitor
Frequent Visitor

Re: Calculating deviation between revenue and daily budget with Direct Query

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

 

Moderator v-qiuyu-msft
Moderator

Re: Calculating deviation between revenue and daily budget with Direct Query

Hi @carinam,

 

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

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
carinam Frequent Visitor
Frequent Visitor

Re: Calculating deviation between revenue and daily budget with Direct Query

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: 

Budget.pngRevenue.png#WorkDays.png

 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

Moderator v-qiuyu-msft
Moderator

Re: Calculating deviation between revenue and daily budget with Direct Query

Hi @carinam,

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
carinam Frequent Visitor
Frequent Visitor

Re: Calculating deviation between revenue and daily budget with Direct Query

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

Highlighted
richbenmintz
Advisor

Re: Calculating deviation between revenue and daily budget with Direct Query

Hi @carinam,

 

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

Allocted Budget by Day.PNG

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 136 members 1,660 guests
Please welcome our newest community members: