Calculating difference between daily revenue and budget with Power BI (DirectQuery)
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:
We are using the following tables:
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
powerbi dax m
edited Nov 12 at 16:49
Alexis Olson
12.3k21633
asked Nov 12 at 9:26
Carina
62
add a comment |
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:
We are using the following tables:
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
powerbi dax m
edited Nov 12 at 16:49
Alexis Olson
12.3k21633
asked Nov 12 at 9:26
Carina
62
You need to put some examples with sample data. Read the community standards for question posting.
– Rahul Neekhra
Nov 12 at 9:52
1
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
Nov 12 at 9:53
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
Nov 12 at 11:47
add a comment |
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:
We are using the following tables:
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
powerbi dax m
edited Nov 12 at 16:49
Alexis Olson
12.3k21633
asked Nov 12 at 9:26
Carina
62
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:
We are using the following tables:
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
powerbi dax m
powerbi dax m
edited Nov 12 at 16:49
Alexis Olson
12.3k21633
asked Nov 12 at 9:26
Carina
62
edited Nov 12 at 16:49
Alexis Olson
12.3k21633
asked Nov 12 at 9:26
Carina
62
edited Nov 12 at 16:49
Alexis Olson
12.3k21633
edited Nov 12 at 16:49
Alexis Olson
12.3k21633
edited Nov 12 at 16:49
Alexis Olson
12.3k21633
12.3k21633
asked Nov 12 at 9:26
Carina
62
asked Nov 12 at 9:26
Carina
62
asked Nov 12 at 9:26
Carina
62
62
You need to put some examples with sample data. Read the community standards for question posting.
– Rahul Neekhra
Nov 12 at 9:52
1
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
Nov 12 at 9:53
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
Nov 12 at 11:47
add a comment |
You need to put some examples with sample data. Read the community standards for question posting.
– Rahul Neekhra
Nov 12 at 9:52
1
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
Nov 12 at 9:53
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
Nov 12 at 11:47
You need to put some examples with sample data. Read the community standards for question posting.
– Rahul Neekhra
Nov 12 at 9:52
You need to put some examples with sample data. Read the community standards for question posting.
– Rahul Neekhra
Nov 12 at 9:52
1
1
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
Nov 12 at 9:53
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
Nov 12 at 9:53
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
Nov 12 at 11:47
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
Nov 12 at 11:47
add a comment |
1 Answer
1
active
oldest
votes
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]
answered Nov 12 at 17:07
Alexis Olson
12.3k21633
Thanks! I tried your solution, but it still doesn't work. I think the problem might be that in the budget table the only date is the first of each month, as that is when the budget is recorded. We tried to copy and merge the date and the budget table to get all dates into the budget table - but that changes the storage mode to import mode, which we cannot work with in this case.
– Carina
Nov 13 at 10:06
It worked fine when I tried to replicate it. Can you share a PBIX?
– Alexis Olson
Nov 13 at 14:44
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53259184%2fcalculating-difference-between-daily-revenue-and-budget-with-power-bi-directque%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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]
answered Nov 12 at 17:07
Alexis Olson
12.3k21633
Thanks! I tried your solution, but it still doesn't work. I think the problem might be that in the budget table the only date is the first of each month, as that is when the budget is recorded. We tried to copy and merge the date and the budget table to get all dates into the budget table - but that changes the storage mode to import mode, which we cannot work with in this case.
– Carina
Nov 13 at 10:06
It worked fine when I tried to replicate it. Can you share a PBIX?
– Alexis Olson
Nov 13 at 14:44
add a comment |
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]
answered Nov 12 at 17:07
Alexis Olson
12.3k21633
Thanks! I tried your solution, but it still doesn't work. I think the problem might be that in the budget table the only date is the first of each month, as that is when the budget is recorded. We tried to copy and merge the date and the budget table to get all dates into the budget table - but that changes the storage mode to import mode, which we cannot work with in this case.
– Carina
Nov 13 at 10:06
It worked fine when I tried to replicate it. Can you share a PBIX?
– Alexis Olson
Nov 13 at 14:44
add a comment |
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]
answered Nov 12 at 17:07
Alexis Olson
12.3k21633
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]
answered Nov 12 at 17:07
Alexis Olson
12.3k21633
answered Nov 12 at 17:07
Alexis Olson
12.3k21633
answered Nov 12 at 17:07
Alexis Olson
12.3k21633
answered Nov 12 at 17:07
Alexis Olson
12.3k21633
12.3k21633
Thanks! I tried your solution, but it still doesn't work. I think the problem might be that in the budget table the only date is the first of each month, as that is when the budget is recorded. We tried to copy and merge the date and the budget table to get all dates into the budget table - but that changes the storage mode to import mode, which we cannot work with in this case.
– Carina
Nov 13 at 10:06
It worked fine when I tried to replicate it. Can you share a PBIX?
– Alexis Olson
Nov 13 at 14:44
add a comment |
Thanks! I tried your solution, but it still doesn't work. I think the problem might be that in the budget table the only date is the first of each month, as that is when the budget is recorded. We tried to copy and merge the date and the budget table to get all dates into the budget table - but that changes the storage mode to import mode, which we cannot work with in this case.
– Carina
Nov 13 at 10:06
It worked fine when I tried to replicate it. Can you share a PBIX?
– Alexis Olson
Nov 13 at 14:44
Thanks! I tried your solution, but it still doesn't work. I think the problem might be that in the budget table the only date is the first of each month, as that is when the budget is recorded. We tried to copy and merge the date and the budget table to get all dates into the budget table - but that changes the storage mode to import mode, which we cannot work with in this case.
– Carina
Nov 13 at 10:06
Thanks! I tried your solution, but it still doesn't work. I think the problem might be that in the budget table the only date is the first of each month, as that is when the budget is recorded. We tried to copy and merge the date and the budget table to get all dates into the budget table - but that changes the storage mode to import mode, which we cannot work with in this case.
– Carina
Nov 13 at 10:06
It worked fine when I tried to replicate it. Can you share a PBIX?
– Alexis Olson
Nov 13 at 14:44
It worked fine when I tried to replicate it. Can you share a PBIX?
– Alexis Olson
Nov 13 at 14:44
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53259184%2fcalculating-difference-between-daily-revenue-and-budget-with-power-bi-directque%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You need to put some examples with sample data. Read the community standards for question posting.
– Rahul Neekhra
Nov 12 at 9:52
1
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
Nov 12 at 9:53
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
Nov 12 at 11:47