Count of New & Exited Members by Channel FYTD

Hi Melissa

How do I remove the solution tag?

Thanks for your reply

Hi,

Can you please help me with my query above?

I have been struggling with this for a while.

Please let me know in case you need any info / clarification.

Thanks

HI @newtopbi. I’m taking a look at your post, but am still coming-up to speed with the issue. In the meantime, what was your experience with @Melissa 's measures from post # 6 in this thread? Greg

Hi @newtopbi. I’ve made some progress I think, but don’t think I have what you’re looking for yet, as there’s a discrepancy between my solution-to-date and the values shown in the pivot tables in your sample data.

(e.g., in your sample for New Members for Sep 2019 [which should display those for the FYTD], both channels MNO and PQR had members who exited in Jul 2019 and members who joined in Aug 2019, and my measure calculates their value as “2” whereas your Excel pivot table shows them as “1”.)

Here are my current measures:

New Members for the month by Channel FYTD v3 = 
VAR _FYStartDate = CALCULATE( MIN( Dates[Date] ), 
    FILTER( ALL( Dates ), Dates[Fiscal Year] = SELECTEDVALUE( Dates[Fiscal Year] ) ) )
VAR _SelectedMonthLastDate = CALCULATE( MAX( Dates[Date] ), 
    FILTER( ALL( Dates ), Dates[Month & Year] = SELECTEDVALUE( Dates[Month & Year] ) ) )
-- get the members for all time
VAR _AllTimeMemberList = CALCULATETABLE( VALUES( 'Member Details'[memberno] ),
    FILTER( ALL( Dates ), 
        Dates[Date] <= _SelectedMonthLastDate ) )
-- get the members prior to the current FY
VAR _PriorToCurrentFYMemberList = CALCULATETABLE( VALUES( 'Member Details'[memberno] ),
    FILTER( ALL( Dates ), 
        Dates[Date] < _FYStartDate ) )
-- new members are those in "all time" who are not in "prior"
VAR _Result = COUNTROWS( EXCEPT( _AllTimeMemberList, _PriorToCurrentFYMemberList ) )

RETURN
_Result


Exited Members for the month by Channel FYTD v3 = 
VAR _FYStartDate = CALCULATE( MIN( Dates[Date] ), 
    FILTER( ALL( Dates ), Dates[Fiscal Year] = SELECTEDVALUE( Dates[Fiscal Year] ) ) )
VAR _SelectedMonthLastDate = CALCULATE( MAX( Dates[Date] ), 
    FILTER( ALL( Dates ), Dates[Month & Year] = SELECTEDVALUE( Dates[Month & Year] ) ) )
-- get the members for current FY
VAR _CurrentFYMemberList = CALCULATETABLE( VALUES( 'Member Details'[memberno] ),
    FILTER( ALL( Dates ), 
        Dates[Date] >= _FYStartDate &&
        Dates[Date] <= _SelectedMonthLastDate ) )
-- get the members prior to the current FY
VAR _PriorToCurrentFYMemberList = CALCULATETABLE( VALUES( 'Member Details'[memberno] ),
    FILTER( ALL( Dates ), 
        Dates[Date] < _FYStartDate ) )
-- exited members are those in "prior" who are not in "current FY"
VAR _Result = COUNTROWS( EXCEPT( _PriorToCurrentFYMemberList, _CurrentFYMemberList ) )

RETURN
_Result

Here is my current PBIX.
eDNA Forum - Count of New & Exited Members by Channel FYTD v1.pbix (94.5 KB)

Can you please confirm if the pivot tables in your sample Excel file are showing the correct values for Sep 2019?

Thanks,
Greg

1 Like

Hi Greg,

Thank you so much for looking into this.

I have had to amend the channel for member # 1002 to GHI for all the months till Aug 2019. Please see the attached amended spreadsheet.
We are trying to capture the new / lost accounts from a business perspective rather than from a channel perspective.

For instance, member # 1004 had JKL as a channel in June but it changes to PQR in July however, it is not a new client from a business perspective but is from a channel perspective. Similarly member # 2003 had ABC as a channel until Aug 2019 but changed to MNO in Sept 2019 however once again it is not a new member for the business.

Hope this helps. I know there are many moving parts to this.

Please let me know if you need any further information.

Much appreciated.

Channel Question New Accounts - PBI FYTD.xlsx (40.0 KB)

Hi @newtopbi. So as I now understand it, you want a single line for the business, not individual rows for each channel; if so, you should get what you’re looking for by removing the Channel column from the tables. Could you please confirm this? If this is not the case, can you please send me a mock-up screenshot of exactly what you are looking for? Thanks, Greg

Hi @newtopbi, we’ve noticed that no response has been received from you since the 28th of July. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi Greg,

I essentially want how the pivot table result looks. New and closed accounts via channel for FYTD. If say June 2020 is selected in the month year slicer then it would show me all the new and closed accounts from 01/07/2019 to 30/06/2020 or if September 2019 is selected in the slicer then from 01/07/2019 to 30/09/2019. However only reporting on new / closed accounts from a business perspective and not considering the inter transfer of accounts from one channel to another because that is not new / closed from a business perspective but is from channel perspective.

I hope this helps. Please let me know should you need any further information.

Apologies for the late response. Have been very busy at work.

Many thanks

HI @newtopbi. I don’t see anything different than removing the channel from the matrices as I noted in my earlier post, assuming that a “closed” account is the same as the term “exited” we’ve used throughout this thread. Here’s the PBIX with channel removed. If this in not what you’re looking for, please provide mock-ups of your desired result.

Greg
eDNA Forum - Count of New & Exited Members by Channel FYTD v2.pbix (94.0 KB)

Hi Greg,

Appreciate you looking into this and looking at the attached pbix file it seems to work.

I will apply this when I go back to work next week and shall keep you posted.

Many thanks

Hi @newtopbi, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @newtopbi, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. 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!

Hi Greg,
Apologies for the late response. Had a pretty full on week.

The formulas for new and exited members for FYTD unfortunately do not work. They consider the inter transfer between the channels as a new members even though they are not new members for the business.

The following formulas work perfectly fine for the monthly calculations:

    New Members for the month by Channel = 
    VAR MemberList = VALUES(MemberDetailsTable[memberno])
    VAR ChurnTime = [Days]
    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 ))

    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] )),
            
    RETURN

    COUNTROWS( 
        EXCEPT( PriorList, MemberList ))

All I am looking for are these numbers to be cumulatively added on FYTD basis. So when July 2019 is selected in the slicer the monthly numbers and the FYTD numbers for new and exited members are the same as July is the start of the FY. When Aug 2019 is selected in the slicer it should add up July 2019 and Aug 2019 and so on.

Could you please look into this matter again and help reach a solution.
Much appreciated.
thank you

Hi @newtopbi. I think we’ve been going in circles a bit. Could you please prepare a mock-up in Excel of exactly the outcome you’re looking for? Greg

Hi @newtopbi, though we appreciate the detailed information you posted, I’m afraid this question is out of the forum’s support boundary. We would like to emphasize that the forum is not a free development service or an on-demand coaching service, asking more than one question in a forum thread and posting questions that could take hours to answer are considered inappropriate. I’ve highlighted some of the key points from the guide to define posts which are not considered appropriate:

  • Asking a question you know could take hours of time to answer.

  • Asking more than one question in any forum thread

  • Asking question after question in the same forum thread around the same project or piece of development work.

More details can be found here - Asking Questions On The Enterprise DNA Support Forum

Hi Greg,

Could I please get back to you with a detailed excel dataset before the end of this week as a part of a final attempt to get a possible solution? I had no idea that this would take as long as it has as I am new to pbi and you guys have been an excellent support to my earlier queries.

One last chance to seek a solution is all I am seeking and would greatly be appreciated.

Please let me know

Many thanks

Sure @newtopbi, no worries. I just think we’ve been iterating and iterating without getting to what you want, so a dataset and mock-up of the desired outcome would give us a target for a last shot. Greg

1 Like

Thank you Greg.
Shall send you the data set and the mock up of the desired result set sometime tomorrow.

PBI Forum Sample Data Final.xlsb (3.9 MB)
Hi Greg,

Please find attached the data set and the mock up of the desired outcome.

All Data tab - has all the data for the period June 2019 to Oct 2019 (columns A to D).

Columns G to O in this tab is the mock up of the desired outcome. So essentially looking for the results that are highlighted in yellow (columns J, K, N & O) as I have the solution for columns H, I, L & M.

Column J - exited members FYTD
Column K - new members FYTD
Column N - Exited members balance FYTD
Column O - New members balance FYTD

I have additional sheets broken down via months to facilitate calculation in excel. The start of the financial year is July every year.

Hope this helps in achieving the solution we are after.

Your assistance is very much appreciated.

Hi @newtopbi. More progress, but not an exact match. To ensure I didn’t use any previous assumptions, I rebuilt the PBIX from scratch, and with @Melissa’s help on the v4 DAX formulae, I’ve come quite close.

I’ve highlighted the matched values (exact or very close) in light blue. (There’s an issue [probably
small one, but I couldn’t find it quickly] with using the same base code for [Exited Balance] as for [Exited Members], but the technique works for “New”, so …)

I’m thinking that the differences between the Excel sample and the PBIX file may be Excel-calculation and data-related, and I didn’t dive into them deeper.

Hopefully this helps you get past the finish line.

Greg
eDNA Forum - Count of New & Exited Members by Channel FYTD v4.pbix (2.7 MB)

1 Like