Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Add up only the first purchase

Really struggling with this one: 

 

I have clients who pay quarterly. For 2020, I need to add up ONLY their first fees. 

 

So if my new clients this year are: 

 

CLIENT    FEE DATE      FEE PAID

Jim10/1/2020$2,700
Sally4/1/2020$1,500
Sally7/1/2020$50
Sally10/1/2020$500
Scott1/1/2020$1,000
Scott4/1/2020$5,000
Scott7/1/2020$3,000
Scott10/1/2020$1,000



I need to be able to show "All first-time fees paid this year are $5,200, which is the sum of each first fee paid by Jim ($2700), Sally ($1500), and Scott ($1000). The table also includes many other ongoing quarterly fees paid by clients who were here prior to 2020. 

 

I've been able to isolate the earliest fee date, but I can't figure out how to say "Add these up only when it's the earliest fee date". 

 

Any help would be much appreciated. 

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi, Anonymous , I think this is what you want,

    Amount first paid = 
SUMX (
    VALUES ( RECEIVABLE[CLIENT] ),
    CALCULATE (
        SUM ( RECEIVABLE[FEEPAID] ),
        VAR __first_date =
            CALCULATE ( MIN ( RECEIVABLE[FEEDATE] ) )
        RETURN
            FILTER ( ALL ( RECEIVABLE[FEEDATE] ), RECEIVABLE[FEEDATE] = __first_date )
    )
)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Sorry, didn't get the notification that there was a reply. Am going to give this a try!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    // A more general measure wil be:
// "Sum up all the very first fees
// for all the currenly visible clients
// within the visible period of time."
// If your selected period will be one year,
// you'll get what you want. If you
// select one month, you'll get the
// first fees within this month and so on.
// Of course, every sensible, non-trivial
// model needs a dedicated Dates table,
// which I assume you do have. I also assume
// that the model is correct, meaning it has
// a fact table and dimensions. One of the
// dimensions is Clients. T is the name of
// the fact table (the one you've shown).
// Clients[ClientID] joins to T[ClientID]
// and Dates[Date] joins to T[Fee Date].

[Sum Of 1st Fees] =
SUMX(
    DISTINCT( Clients[ClientID] ),
    FIRSTNONBLANKVALUE(
        Dates[Date],
        CALCULATE( SUM( T[Fee Paid] ) )
    )
)