Help with Report

[Anonymous]

I have a report in which one tab shows new accounts and accounts that dropped out of the data.  They are shown in two separate tables.  There is a slicer on the page for Quarter.

Example:

Account	Quarter	Exposure
ABC	21Q1	100
ABC	21Q2	500
EFG	21Q1	200
XYZ	21Q2	800

In this example ABC and EFG would be new in 21Q1, XYZ would be new in 21Q2, and EFG would be "dropped out" in 21Q2.  The problem I have is that if I select 21Q2 I want to see XYZ in the new accounts table, and EFG in the dropped out accounts table.  However, since the quarter for EFG is 21Q1, it does not appear.

If anyone has any suggestions, I would greatly appreciate it.  Thanks!

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Please try the following steps:

1. Create a calculation table

Quarter = SUMMARIZE('Table',[Quarter])

And to create a slicer with the summarized quarter field.

2. Create a calculated column

YQ_index = YEAR('Table'[Date])*10+QUARTER('Table'[Date])

3. Create a measure

NEW/OLD/OUT = 

VAR _CURRENT_T=FILTER(ALL('Table'),'Table'[Quarter]=SELECTEDVALUE('Quarter'[Quarter]))
VAR _CURRENT_YQ=MAXX(_CURRENT_T,[YQ_index])

VAR _CURRENT_Q_List=SUMMARIZE(_CURRENT_T,[Account])

VAR _PRE_YQ=MAXX(FILTER(ALL('Table'),'Table'[YQ_index]<_CURRENT_YQ),[YQ_index])
VAR _PRE_Q_List=SUMMARIZE(FILTER(ALL('Table'),'Table'[YQ_index]=_PRE_YQ),[Account])

VAR _CURRENT_Account=MAX('Table'[Account])
VAR _IF=
        SWITCH(
            TRUE(),
            _CURRENT_Account IN _CURRENT_Q_List && _CURRENT_Account IN _PRE_Q_List,"OLD",
            _CURRENT_Account IN _CURRENT_Q_List && NOT(_CURRENT_Account IN _PRE_Q_List),"NEW",
            NOT(_CURRENT_Account IN _CURRENT_Q_List) && _CURRENT_Account IN _PRE_Q_List,"OUT")
return _IF

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I am assuming you mean new visual table.

Seem very similar to the approach of a new customers, a lost customers. You need to have period as qtr

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497

[Anonymous]

I'm not sure what you mean by needing to have period as quarter.  I am already using quarter, but it doesn't work.