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:

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