New Customers Count by Channel

Channel Question New Accounts - PBI - Forum.xlsx (12.2 KB)

Hi,

I’m new to this forum and this is my first post and I am hoping this works. I have a question with regards to calculating new customers via channel. Please see the attached.

Thank you

Hi @newtopbi,

So thanks for the detailed xlsx an additional PBIX would have been appreciated as well :wink:
I set up a basic model with your MemberDetailsTable and a Dates table. The ChurnTime was a guess because you didn’t state it’s value explicitly. Created the following measure:

New Members for the month by Channel = 
VAR MemberList = VALUES(MemberDetailsTable[memberno])
VAR ChurnTime = 30
VAR PriorList =
    CALCULATETABLE( VALUES( MemberDetailsTable[memberno] ),
        FILTER(ALL( Dates ), Dates[Date]> ( MIN( Dates[Date] ) - ChurnTime ) && Dates[Date] < MIN(Dates[Date] )),
        ALLEXCEPT( MemberDetailsTable, MemberDetailsTable[memberno], Dates ))
RETURN

COUNTROWS( 
    EXCEPT( MemberList, PriorList ))

With this result.
image
.

I hope this is helpful. Here’s my sample file.
New Customers Count by Channel.pbix (106.1 KB)

1 Like

Hi Melissa,
Thank you so much for your prompt response. I have been able to successfully calculate the new members by channel as well as new members balance by channel.
However, I am stuck with calculating the exited members and their balance by channel. Instead of showing 2 as a count it is showing me 3 when the row context is channel. Please see the attached file.
Your assistance is much appreciated.
Thank you :slight_smile:
New & Exited Customers Count by Channel.pbix (113.4 KB)

@newtopbi,

Right. See if this does the trick for you.

Exited Members for the month by Channel = 
VAR MemberList = CALCULATETABLE( VALUES(MemberDetailsTable[memberno])| ALLEXCEPT( MemberDetailsTable| MemberDetailsTable[memberno]| Dates ))
VAR ChurnTime = 30
VAR PriorList =
    CALCULATETABLE( VALUES( MemberDetailsTable[memberno] )|
        FILTER(ALL( Dates )| Dates[Date]> ( MIN( Dates[Date] ) - ChurnTime ) && Dates[Date] < MIN(Dates[Date] ))|
        ALLEXCEPT( MemberDetailsTable| MemberDetailsTable[memberno] | Dates ))
RETURN

COUNTROWS( 
    EXCEPT( PriorList| MemberList ))

.
Here are some resources you might want to explore further:
https://info.enterprisedna.co/dax-function-guide/allexcept/

https://info.enterprisedna.co/dax-function-guide/calculatetable/

I hope this is helpful

Thanks a ton Melissa for your prompt response as well as the solution and for sharing the relevant videos.

Much appreciated

It’s great to know that you are making progress with your query @newtopbi. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

1 Like

Hi Melissa,

Both new members for the month by channel and exited members for the month by channel provided by you work perfectly well. Thank you.

I would really appreciate your assistance in calculating the FYTD new members for the month by channel and FYTD exited members for the month by channel with the current filter on Month Year Slicer where the FY start date is 01/07. So for when the month year selected in the slicer say is Aug 2019 - it should provide me with the new and exited members by channel for both July 2019 and Aug 2019 and accordingly for the Month Year June 2020 - it should reflect the total new and exited members by channel since 01/07/2019 till 30/06/2020.

My formula for New Members for the month by Channel FYTD = CALCULATE([New Members for the month by Channel],DATESYTD(Dates[Date],“30/06”)) does not work and rightly so. However, I am struggling to find a solution here.

Thank you in advance for your assistance.

Regards,
newtopbi
New & Exited Customers Count by Channel.pbix (3.1 MB)

@newtopbi,

According to forum guidelines would you be so kind to create a new topic for your follow up question? Thanks!

I’m away now but will look into it later tonight, if your question has remaind unanswered.