Need total sales when client is new in 2020

[Anonymous]

Hi - am having a terrible time with this one, and I feel like it should be so easy.

I need BILLED AMOUNT only if the client's first BILLED DATE is in 2020. It tells us how much new cash we are collecting this year.

I tried

IF(MIN([BILLEDDATE])>=DATE(2020,1,1),sum([BILLEDAMOUNT]),0)

but while that gives me each client's fees individually, the Total = $0.

I tried adding a measure that was MIN([BILLEDDATE]) and then doing

IF([EARLIEST FEE DATE]>=DATE(2020,1,1),SUM([BILLEDAMOUNT]),0)

but that gave me similar results.

I've tried other variations of columns and measures, but all are to no avail.

What am I doing wrong here?

Thank you!

[Anonymous · Accepted solution]

// T is your fact table and I assume
// the model is correctly built, that is,
// it's a proper star schema with dimensions
// and facts. If not, then you have to
// transform it into a correct one.

// Main measure
[Billed Amount] = SUM( T[Billed Amount] )

// The one you want...
[Billed Amount 2020] =
var __firstDate = DATE( 2020, 1, 1 )
var __relevantCustomers =
    FILTER(
        // Your dimension should not contain
        // a blank row. The model should not
        // have referential integrity problems.
        DISTINCT( Customer[CustomerID] ),
        // Get only those that have their first
        // transaction in 2020.
        CALCULATE(
            ISEMPTY( T ),
            // 'Billed Date' should be a date
            // table marked as such in the model.
            'Billed Date'[Date] < __firstDate,
            ALLEXCEPT( T, Customer )
        )
    )
return
    CALCULATE(
        [Billed Amount],
        __relevantCustomers,
        ALL( Customers )
    )

[Anonymous]

Wow, that worked! And I don't think I ever would have figured it out on my own (well, maybe in a couple years 😁), I can't thank you enough! Solves a huge challenge for our reporting, thank you!

[amitchandak]

@Anonymous , I think it it is better you have a first BILLEDDATE as column

example

First BILLEDDATE = minx(filter(Table,[client ID] = earlier[Clinet ID]),[BILLEDDATE] )

now you filter are First BILLEDDATE  last year and BILLEDDATE   this year. If you can have slicer on both you can control by that

or have a formula like

new measure =
Var _min = Year(today())-1
Var _max = Year(today())

return
calculate(sum([BILLEDAMOUNT]), filter(Table ,year([First BILLEDDATE]) = _min && year([BILLEDDATE]) =_max))

or use all or allselected

new measure =
Var _min = Year(today())-1
Var _max = Year(today())

return
calculate(sum([BILLEDAMOUNT]), filter(allselected(Table) ,year([First BILLEDDATE]) = _min && year([BILLEDDATE]) =_max))

Similar approach I discussed here for cohort: https://youtu.be/Q1vPWmfI25o?t=755

[lbendlin]

Your slightly forgetting that little thing called Filter Context.

IF(MIN([BILLEDDATE])>=DATE(2020,1,1),sum([BILLEDAMOUNT]),0)

works on the current row. It works, but doesn't give you the expected result.

Instead, look at the problem from the perspective of the account, not of the individual transaction.  Or, even more generic (if you also want nice totals), look at the problem from the perspective of the Totals row, ie a full collection of all accounts and their transactions.

Here's the meta approach

- Get a table variable with all account names in scope

- get the minimum billed amount for each (via MINX, or CALCULATE), and the total amount for each (via SUMX or CALCULATE)

- apply a filter to sumx only the sumx where the minx is in the current year)

That works well for the total, and while it seems to be overkill for the individual account it will still work! It won't work well for the individual transaction as you will see the same total value all over.