Power BI DAX Calculation based on month selected in slicer
I need several measures in my report. The measure I want to start with is count of distinct ID four months before the month I selected on (e.g if I select on Aug 2018, the calculation would be all distinct ID before 30/04/2018). The reason I'm doing this is later on I also want to use this same slicer to work on count of ID within the four month period based on the selection.
Here is my DAX Calculation with comments:
Count four months ago =
// Find the end date of the month
VAR end_of_last_quarter =
FORMAT ( EOMONTH ( MAX ( 'Calendar'[Date] ), -4 ), "dd/mm/yyyy" )
RETURN
// Count distinct ID on or before that date
CALCULATE (
DISTINCTCOUNT ( 'Report Data'[Id] ),
FORMAT ( 'Report Data'[REPORT DATE], "d/mm/yyyy" )
<= FORMAT ( end_of_last_quarter, "d/mm/yyyy" )
)
& " Reports before "
& end_of_last_quarter
However after checking this calculation, it seems it only gives me the number of counts in the month I selected:
The screenshot tells me there are 12 report in Apr 2018, rather than the right number before 31/12/2017.
Thanks in advance for any ideas
powerbi dax slicers
edited Nov 26 '18 at 10:27
Marco Vos
1,998149
asked Nov 26 '18 at 2:49
LamboLambo
425726
add a comment |
I need several measures in my report. The measure I want to start with is count of distinct ID four months before the month I selected on (e.g if I select on Aug 2018, the calculation would be all distinct ID before 30/04/2018). The reason I'm doing this is later on I also want to use this same slicer to work on count of ID within the four month period based on the selection.
Here is my DAX Calculation with comments:
Count four months ago =
// Find the end date of the month
VAR end_of_last_quarter =
FORMAT ( EOMONTH ( MAX ( 'Calendar'[Date] ), -4 ), "dd/mm/yyyy" )
RETURN
// Count distinct ID on or before that date
CALCULATE (
DISTINCTCOUNT ( 'Report Data'[Id] ),
FORMAT ( 'Report Data'[REPORT DATE], "d/mm/yyyy" )
<= FORMAT ( end_of_last_quarter, "d/mm/yyyy" )
)
& " Reports before "
& end_of_last_quarter
However after checking this calculation, it seems it only gives me the number of counts in the month I selected:
The screenshot tells me there are 12 report in Apr 2018, rather than the right number before 31/12/2017.
Thanks in advance for any ideas
powerbi dax slicers
edited Nov 26 '18 at 10:27
Marco Vos
1,998149
asked Nov 26 '18 at 2:49
LamboLambo
425726
1
InFORMAT('Report Data'[REPORT DATE], "d/mm/yyyy")<=FORMAT(end_of_last_quarter, "d/mm/yyyy")you are doing order comparison of strings, not of dates. Consider removing theFORMAT. Similarly, remove the FORMAT fromend_of_last_quarter, so you are working with dates rather than strings.
– Mankarse
Nov 26 '18 at 3:02
@Mankarse Thanks for the suggestion. It works OK and give me the correct number. However when I want to use a matrix visualization and put a field on the row and this measure on the value, it won't give me proper breakdown (i.e. sum of count for each category is not equal to total).
– Lambo
Nov 27 '18 at 7:42
add a comment |
I need several measures in my report. The measure I want to start with is count of distinct ID four months before the month I selected on (e.g if I select on Aug 2018, the calculation would be all distinct ID before 30/04/2018). The reason I'm doing this is later on I also want to use this same slicer to work on count of ID within the four month period based on the selection.
Here is my DAX Calculation with comments:
Count four months ago =
// Find the end date of the month
VAR end_of_last_quarter =
FORMAT ( EOMONTH ( MAX ( 'Calendar'[Date] ), -4 ), "dd/mm/yyyy" )
RETURN
// Count distinct ID on or before that date
CALCULATE (
DISTINCTCOUNT ( 'Report Data'[Id] ),
FORMAT ( 'Report Data'[REPORT DATE], "d/mm/yyyy" )
<= FORMAT ( end_of_last_quarter, "d/mm/yyyy" )
)
& " Reports before "
& end_of_last_quarter
However after checking this calculation, it seems it only gives me the number of counts in the month I selected:
The screenshot tells me there are 12 report in Apr 2018, rather than the right number before 31/12/2017.
Thanks in advance for any ideas
powerbi dax slicers
edited Nov 26 '18 at 10:27
Marco Vos
1,998149
asked Nov 26 '18 at 2:49
LamboLambo
425726
I need several measures in my report. The measure I want to start with is count of distinct ID four months before the month I selected on (e.g if I select on Aug 2018, the calculation would be all distinct ID before 30/04/2018). The reason I'm doing this is later on I also want to use this same slicer to work on count of ID within the four month period based on the selection.
Here is my DAX Calculation with comments:
Count four months ago =
// Find the end date of the month
VAR end_of_last_quarter =
FORMAT ( EOMONTH ( MAX ( 'Calendar'[Date] ), -4 ), "dd/mm/yyyy" )
RETURN
// Count distinct ID on or before that date
CALCULATE (
DISTINCTCOUNT ( 'Report Data'[Id] ),
FORMAT ( 'Report Data'[REPORT DATE], "d/mm/yyyy" )
<= FORMAT ( end_of_last_quarter, "d/mm/yyyy" )
)
& " Reports before "
& end_of_last_quarter
However after checking this calculation, it seems it only gives me the number of counts in the month I selected:
The screenshot tells me there are 12 report in Apr 2018, rather than the right number before 31/12/2017.
Thanks in advance for any ideas
powerbi dax slicers
powerbi dax slicers
edited Nov 26 '18 at 10:27
Marco Vos
1,998149
asked Nov 26 '18 at 2:49
LamboLambo
425726
edited Nov 26 '18 at 10:27
Marco Vos
1,998149
asked Nov 26 '18 at 2:49
LamboLambo
425726
edited Nov 26 '18 at 10:27
Marco Vos
1,998149
edited Nov 26 '18 at 10:27
Marco Vos
1,998149
edited Nov 26 '18 at 10:27
Marco Vos
1,998149
1,998149
asked Nov 26 '18 at 2:49
LamboLambo
425726
asked Nov 26 '18 at 2:49
LamboLambo
425726
asked Nov 26 '18 at 2:49
LamboLambo
425726
425726
1
InFORMAT('Report Data'[REPORT DATE], "d/mm/yyyy")<=FORMAT(end_of_last_quarter, "d/mm/yyyy")you are doing order comparison of strings, not of dates. Consider removing theFORMAT. Similarly, remove the FORMAT fromend_of_last_quarter, so you are working with dates rather than strings.
– Mankarse
Nov 26 '18 at 3:02
@Mankarse Thanks for the suggestion. It works OK and give me the correct number. However when I want to use a matrix visualization and put a field on the row and this measure on the value, it won't give me proper breakdown (i.e. sum of count for each category is not equal to total).
– Lambo
Nov 27 '18 at 7:42
add a comment |
1
InFORMAT('Report Data'[REPORT DATE], "d/mm/yyyy")<=FORMAT(end_of_last_quarter, "d/mm/yyyy")you are doing order comparison of strings, not of dates. Consider removing theFORMAT. Similarly, remove the FORMAT fromend_of_last_quarter, so you are working with dates rather than strings.
– Mankarse
Nov 26 '18 at 3:02
@Mankarse Thanks for the suggestion. It works OK and give me the correct number. However when I want to use a matrix visualization and put a field on the row and this measure on the value, it won't give me proper breakdown (i.e. sum of count for each category is not equal to total).
– Lambo
Nov 27 '18 at 7:42
1
1
In
FORMAT('Report Data'[REPORT DATE], "d/mm/yyyy")<=FORMAT(end_of_last_quarter, "d/mm/yyyy") you are doing order comparison of strings, not of dates. Consider removing the FORMAT. Similarly, remove the FORMAT from end_of_last_quarter, so you are working with dates rather than strings.– Mankarse
Nov 26 '18 at 3:02
In
FORMAT('Report Data'[REPORT DATE], "d/mm/yyyy")<=FORMAT(end_of_last_quarter, "d/mm/yyyy") you are doing order comparison of strings, not of dates. Consider removing the FORMAT. Similarly, remove the FORMAT from end_of_last_quarter, so you are working with dates rather than strings.– Mankarse
Nov 26 '18 at 3:02
@Mankarse Thanks for the suggestion. It works OK and give me the correct number. However when I want to use a matrix visualization and put a field on the row and this measure on the value, it won't give me proper breakdown (i.e. sum of count for each category is not equal to total).
– Lambo
Nov 27 '18 at 7:42
@Mankarse Thanks for the suggestion. It works OK and give me the correct number. However when I want to use a matrix visualization and put a field on the row and this measure on the value, it won't give me proper breakdown (i.e. sum of count for each category is not equal to total).
– Lambo
Nov 27 '18 at 7:42
add a comment |
0
active
oldest
votes
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%2f53474180%2fpower-bi-dax-calculation-based-on-month-selected-in-slicer%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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%2f53474180%2fpower-bi-dax-calculation-based-on-month-selected-in-slicer%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
1
In
FORMAT('Report Data'[REPORT DATE], "d/mm/yyyy")<=FORMAT(end_of_last_quarter, "d/mm/yyyy")you are doing order comparison of strings, not of dates. Consider removing theFORMAT. Similarly, remove the FORMAT fromend_of_last_quarter, so you are working with dates rather than strings.– Mankarse
Nov 26 '18 at 3:02
@Mankarse Thanks for the suggestion. It works OK and give me the correct number. However when I want to use a matrix visualization and put a field on the row and this measure on the value, it won't give me proper breakdown (i.e. sum of count for each category is not equal to total).
– Lambo
Nov 27 '18 at 7:42