Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Report

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:

AccountQuarterExposure
ABC21Q1100
ABC21Q2500
EFG21Q1200
XYZ21Q2800

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!

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.