How can I calculate the distinct count of a customerID during the last six months DAX? I need to see if customers have transactions
I have a table in my database that creates records based on customers billing.
I decided to create a rolling calendar and add a relationship to the table, where my customer IDs are located. I have tried to find the count of ID records with:
Customer Base =
IF (
HASONEVALUE ( Ddcca[Clienteid] ),
CALCULATE (
DISTINCTCOUNT ( Ddcca[Clienteid] ),
DATESBETWEEN (
Rolling_Calendar[Date],
DATEADD ( STARTOFMONTH ( Rolling_Calendar[Date] ), -6, MONTH ),
STARTOFMONTH ( Rolling_Calendar[Date] )
)
),
MAXX (
VALUES ( Rolling_Calendar[Date] ),
CALCULATE (
DISTINCTCOUNT ( Ddcca[Clienteid] ),
DATESBETWEEN (
Rolling_Calendar[Date],
DATEADD ( STARTOFMONTH ( Rolling_Calendar[Date] ), -17, MONTH ),
STARTOFMONTH ( Rolling_Calendar[Date] )
)
)
)
)
My rolling calendar includes non repeating dates, when I try to create a matrix in powerBI I get error:
Cant display visual
.
Rolling calendar connection
Rolling calendar is up to todays date
powerbi dax powerpivot
asked Nov 15 '18 at 15:47
Diego CastilloDiego Castillo
63
add a comment |
I have a table in my database that creates records based on customers billing.
I decided to create a rolling calendar and add a relationship to the table, where my customer IDs are located. I have tried to find the count of ID records with:
Customer Base =
IF (
HASONEVALUE ( Ddcca[Clienteid] ),
CALCULATE (
DISTINCTCOUNT ( Ddcca[Clienteid] ),
DATESBETWEEN (
Rolling_Calendar[Date],
DATEADD ( STARTOFMONTH ( Rolling_Calendar[Date] ), -6, MONTH ),
STARTOFMONTH ( Rolling_Calendar[Date] )
)
),
MAXX (
VALUES ( Rolling_Calendar[Date] ),
CALCULATE (
DISTINCTCOUNT ( Ddcca[Clienteid] ),
DATESBETWEEN (
Rolling_Calendar[Date],
DATEADD ( STARTOFMONTH ( Rolling_Calendar[Date] ), -17, MONTH ),
STARTOFMONTH ( Rolling_Calendar[Date] )
)
)
)
)
My rolling calendar includes non repeating dates, when I try to create a matrix in powerBI I get error:
Cant display visual
.
Rolling calendar connection
Rolling calendar is up to todays date
powerbi dax powerpivot
asked Nov 15 '18 at 15:47
Diego CastilloDiego Castillo
63
Please post an image of your rolling calendar table, and explain how you connect it to the main table.
– RADO
Nov 15 '18 at 23:46
I just posted the images you asked for.
– Diego Castillo
Nov 16 '18 at 14:44
add a comment |
I have a table in my database that creates records based on customers billing.
I decided to create a rolling calendar and add a relationship to the table, where my customer IDs are located. I have tried to find the count of ID records with:
Customer Base =
IF (
HASONEVALUE ( Ddcca[Clienteid] ),
CALCULATE (
DISTINCTCOUNT ( Ddcca[Clienteid] ),
DATESBETWEEN (
Rolling_Calendar[Date],
DATEADD ( STARTOFMONTH ( Rolling_Calendar[Date] ), -6, MONTH ),
STARTOFMONTH ( Rolling_Calendar[Date] )
)
),
MAXX (
VALUES ( Rolling_Calendar[Date] ),
CALCULATE (
DISTINCTCOUNT ( Ddcca[Clienteid] ),
DATESBETWEEN (
Rolling_Calendar[Date],
DATEADD ( STARTOFMONTH ( Rolling_Calendar[Date] ), -17, MONTH ),
STARTOFMONTH ( Rolling_Calendar[Date] )
)
)
)
)
My rolling calendar includes non repeating dates, when I try to create a matrix in powerBI I get error:
Cant display visual
.
Rolling calendar connection
Rolling calendar is up to todays date
powerbi dax powerpivot
asked Nov 15 '18 at 15:47
Diego CastilloDiego Castillo
63
I have a table in my database that creates records based on customers billing.
I decided to create a rolling calendar and add a relationship to the table, where my customer IDs are located. I have tried to find the count of ID records with:
Customer Base =
IF (
HASONEVALUE ( Ddcca[Clienteid] ),
CALCULATE (
DISTINCTCOUNT ( Ddcca[Clienteid] ),
DATESBETWEEN (
Rolling_Calendar[Date],
DATEADD ( STARTOFMONTH ( Rolling_Calendar[Date] ), -6, MONTH ),
STARTOFMONTH ( Rolling_Calendar[Date] )
)
),
MAXX (
VALUES ( Rolling_Calendar[Date] ),
CALCULATE (
DISTINCTCOUNT ( Ddcca[Clienteid] ),
DATESBETWEEN (
Rolling_Calendar[Date],
DATEADD ( STARTOFMONTH ( Rolling_Calendar[Date] ), -17, MONTH ),
STARTOFMONTH ( Rolling_Calendar[Date] )
)
)
)
)
My rolling calendar includes non repeating dates, when I try to create a matrix in powerBI I get error:
Cant display visual
.
Rolling calendar connection
Rolling calendar is up to todays date
powerbi dax powerpivot
powerbi dax powerpivot
asked Nov 15 '18 at 15:47
Diego CastilloDiego Castillo
63
asked Nov 15 '18 at 15:47
Diego CastilloDiego Castillo
63
edited Nov 16 '18 at 14:43
Diego Castillo
asked Nov 15 '18 at 15:47
Diego CastilloDiego Castillo
63
asked Nov 15 '18 at 15:47
Diego CastilloDiego Castillo
63
asked Nov 15 '18 at 15:47
Diego CastilloDiego Castillo
63
63
Please post an image of your rolling calendar table, and explain how you connect it to the main table.
– RADO
Nov 15 '18 at 23:46
I just posted the images you asked for.
– Diego Castillo
Nov 16 '18 at 14:44
add a comment |
Please post an image of your rolling calendar table, and explain how you connect it to the main table.
– RADO
Nov 15 '18 at 23:46
I just posted the images you asked for.
– Diego Castillo
Nov 16 '18 at 14:44
Please post an image of your rolling calendar table, and explain how you connect it to the main table.
– RADO
Nov 15 '18 at 23:46
Please post an image of your rolling calendar table, and explain how you connect it to the main table.
– RADO
Nov 15 '18 at 23:46
I just posted the images you asked for.
– Diego Castillo
Nov 16 '18 at 14:44
I just posted the images you asked for.
– Diego Castillo
Nov 16 '18 at 14:44
add a comment |
2 Answers
2
active
oldest
votes
First create the continues date table separately. Then connect the dates table to your current table, and it will solve the issue.
answered Nov 15 '18 at 16:19
PiyushPiyush
62
I tried but it didn´t work.
– Diego Castillo
Nov 15 '18 at 16:32
add a comment |
have you told powerBi the date table is a date dimension?
I generally find i have more luck getting help if i create a simple example in powerbi, save it to oneDrive/Google Drive and paste the link along with the question.
Its can take a bit of time, but there are usually more answers and they are of higher quality.
answered Nov 16 '18 at 15:26
wilson_smythwilson_smyth
353111
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%2f53323076%2fhow-can-i-calculate-the-distinct-count-of-a-customerid-during-the-last-six-month%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
First create the continues date table separately. Then connect the dates table to your current table, and it will solve the issue.
answered Nov 15 '18 at 16:19
PiyushPiyush
62
I tried but it didn´t work.
– Diego Castillo
Nov 15 '18 at 16:32
add a comment |
First create the continues date table separately. Then connect the dates table to your current table, and it will solve the issue.
answered Nov 15 '18 at 16:19
PiyushPiyush
62
I tried but it didn´t work.
– Diego Castillo
Nov 15 '18 at 16:32
add a comment |
First create the continues date table separately. Then connect the dates table to your current table, and it will solve the issue.
answered Nov 15 '18 at 16:19
PiyushPiyush
62
First create the continues date table separately. Then connect the dates table to your current table, and it will solve the issue.
answered Nov 15 '18 at 16:19
PiyushPiyush
62
answered Nov 15 '18 at 16:19
PiyushPiyush
62
answered Nov 15 '18 at 16:19
PiyushPiyush
62
answered Nov 15 '18 at 16:19
PiyushPiyush
62
62
I tried but it didn´t work.
– Diego Castillo
Nov 15 '18 at 16:32
add a comment |
I tried but it didn´t work.
– Diego Castillo
Nov 15 '18 at 16:32
I tried but it didn´t work.
– Diego Castillo
Nov 15 '18 at 16:32
I tried but it didn´t work.
– Diego Castillo
Nov 15 '18 at 16:32
add a comment |
have you told powerBi the date table is a date dimension?
I generally find i have more luck getting help if i create a simple example in powerbi, save it to oneDrive/Google Drive and paste the link along with the question.
Its can take a bit of time, but there are usually more answers and they are of higher quality.
answered Nov 16 '18 at 15:26
wilson_smythwilson_smyth
353111
add a comment |
have you told powerBi the date table is a date dimension?
I generally find i have more luck getting help if i create a simple example in powerbi, save it to oneDrive/Google Drive and paste the link along with the question.
Its can take a bit of time, but there are usually more answers and they are of higher quality.
answered Nov 16 '18 at 15:26
wilson_smythwilson_smyth
353111
add a comment |
have you told powerBi the date table is a date dimension?
I generally find i have more luck getting help if i create a simple example in powerbi, save it to oneDrive/Google Drive and paste the link along with the question.
Its can take a bit of time, but there are usually more answers and they are of higher quality.
answered Nov 16 '18 at 15:26
wilson_smythwilson_smyth
353111
have you told powerBi the date table is a date dimension?
I generally find i have more luck getting help if i create a simple example in powerbi, save it to oneDrive/Google Drive and paste the link along with the question.
Its can take a bit of time, but there are usually more answers and they are of higher quality.
answered Nov 16 '18 at 15:26
wilson_smythwilson_smyth
353111
answered Nov 16 '18 at 15:26
wilson_smythwilson_smyth
353111
answered Nov 16 '18 at 15:26
wilson_smythwilson_smyth
353111
answered Nov 16 '18 at 15:26
wilson_smythwilson_smyth
353111
353111
add a comment |
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.
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%2f53323076%2fhow-can-i-calculate-the-distinct-count-of-a-customerid-during-the-last-six-month%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
Please post an image of your rolling calendar table, and explain how you connect it to the main table.
– RADO
Nov 15 '18 at 23:46
I just posted the images you asked for.
– Diego Castillo
Nov 16 '18 at 14:44