cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carinam
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
Community Support
Community Support

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

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.

Community Support
Community Support

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

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

 

Community Support
Community Support

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.
Highlighted
carinam
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

Community Support
Community Support

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

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

richbenmintz Solution Supplier
Solution Supplier

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors