Both new members for the month by channel and exited members for the month by channel provided by you worked perfectly well for my earlier post. 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.
First I declared month 7 as your FYStartMonth in the Date table.
However it’s not completely clear to me, what you expect - so can you give some more insight on how you want this pattern to work? This is what it’s doing currently:
This formula is something you provided to me last time and works perfectly well for individual month year selection within a slicer with the only thing I altered in your formula was the ChurnTime to include a new measure called Days where Days = DAY(EOMONTH( MAX(MemberDetailsTable[Date]),0)) so it automatically takes the # of days in a month be it 30 or 31 etc in my final report. So it works fine for an individual month.
However, when a single month year is selected in the slicer I would like it to also calculate how many new members joined us via a specific channel for FYTD. Same logic applies to the exited members. So when the slicer selection is say Aug 2019; it should provide new and exited members from 01/07/2019 to 31/08/2019 or when the month year selected is May 2020 it should provide new and exited members from 01/07/2020 to 31/05/2020. So probably the churn window / some portion of the formula needs to be altered than what it currently is. I had attached the PBI file - was that of any help?
So the final table when a particular month is selected on the slicer is as :
Slicer Selection: Single MonthYear: May2020 and with the following columns in the table:
Channel
New Members by Month – works fine
Exited Members by Month – works fine
New Members FYTD – needs a solution
Exited Members FYTD – needs a solution
Please let me know if you need any further information.
Let’s stick with the example. So the VAR MemberList should be the YTD members from the selected Month & Year. But how do you want to determine the PriorList? Should that be the same number of months prior as are currently in the YTD window, the same YTD LY period or the entire previous FY?
Yes, I think PriorList Should that be the same number of months prior as are currently in the FYTD window. We are simply looking at adding all the new & exited members that we are currently calculating on a monthly basis on cumulative basis for FYTD. Hope this helps.
Hi Melissa,
Thank you for your message. I have now attached an excel file that has more data as well as the results that I am getting from the formulas provided above.
As per the attached excel spreadsheet in the FYTD tab, I have included dummy data from 31/05/19 to 30/09/19. I am looking at achieving the final results as indicated by the pivot tables for lost and new members when say Sept 2019 or Aug 2019 is selected in the Month Year slicer.
The NewAccounts tab indicates the results I am getting using the above formulas in my report. I used the formula you provided and then applied it and found that for the month of July 2019, the FYTD figures don’t show up and the figures were not looking right for the subsequent months. So I tweaked the formula a bit as shown in row #40. Numbers looked slightly better but are not accurate. I could not resolve the issue that the FYTD column does not pick numbers for Jul 2019 and the cumulative #s for subsequent months are not correct either as highlighted by the difference in #s.
Also, please refer ClosedAccounts tab. It’s not picking up the correct July closed member numbers for the FYTD column and have differences for the subsequent months (slightly tweaked the formula).
Essentially, the numbers for the month of July 2019 should be the same for FYTD column as it is the first month of the FY when the MonthYear selection is July 2019 in the slicer.
I am hoping this throws more light at the challenge at hand and makes it bit easier to come up with a solution. Should you need any more information then please let me know.
No worries Melissa. Thank you for letting me know.
I have slightly tweaked my dummy data. So when you do get a chance could you please use the one attached herewith. Channel Question New Accounts - PBI FYTD.xlsx (39.8 KB)
Sorry I haven’t got a solution for you yet but maybe some fresh eyes will help resolve this more quickly… @BrianJ, @Greg, @Harsh and all other forum members please feel free to jump in.
.
@newtopbi, this topic is marked as Solved, making it hard to find - can you please remove the Solution tag. 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
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.
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!
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.
