Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

First Sales Date with Sub Accounts

Hello! I'm tracking the 2018 sales for customers whose first date of sale was in 2016 or 2017. Basically I'm looking at new customers from 2016/17 and how they are doing this year. That seems easy e...
Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi Anonymous,

     

    In your scenario, you want to get the earliest date based on each SubAccount group. Right?

     

    To achieve this, you can try to use ALLEXCEPT() function to make groups. Please refer to following calculated column:

     

    Earliest Date =
CALCULATE (
    MIN ( 'First Sales'[First Sale Date] ),
    ALLEXCEPT ( 'First Sales', 'First Sales'[SubAccount] )
)

     

    Thanks,
    Xi Jin.

v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

Hi Anonymous,

 

In your scenario, you want to get the earliest date based on each SubAccount group. Right?

 

To achieve this, you can try to use ALLEXCEPT() function to make groups. Please refer to following calculated column:

 

Earliest Date =
CALCULATE (
    MIN ( 'First Sales'[First Sale Date] ),
    ALLEXCEPT ( 'First Sales', 'First Sales'[SubAccount] )
)

 

Thanks,
Xi Jin.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hello v-xjiin-msft,

     

    Thank you so much! This calculation is so much simpler than what I had tried. This calculation achieves exactly what I wanted.

     

    If you don't mind, could you explain the detail in how this works? I had tried ALLEXCEPT() before and it wasn't working out, is this just preventing the 'First Sales' table from breaking up the 'SubAccount' groups before the MIN() function completes?

     

    Thanks again,