Sum of delta of new customers

[Beekhuis]

Hi, 

I am trying to SUM the delta between LTM this year and LTM previous year for new customers. I see the values when I create a table and include all my measures.

I do not know how to SUM only the Delta of the new customers, because when I create a new measure to SUM the delta and include measures into my filters they seem to not be aware of the context of the customers.

I have a Date/Calendar table and a Sales table. The Sales table is very simple has the following headers: customer, revenue, date. 

I created the following measures:

Revenue LTM = 
CALCULATE (
    SUM ( Sales[revenue] ),
    DATESINPERIOD ( Date[date] , MAX ( Date[date] ), -12 , MONTH )
)

Revenue LTM previous month = 
CALCULATE (
    SUM ( Sales[Revenue] ),
    DATESBETWEEN( Date[date] , LASTDATE(DATEADD(Date[date] , -13 , MONTH)) , LASTDATE(DATEADD(Date[date] , -1 , MONTH)))
)

Delta LTM = [Revenue LTM] - [Revenue LTM previous month]

New Customers LTM = IF([Revenue LTM] && ISBLANK([Revenue LTM previous month]) , "Yes" , "No" )

I am trying to something like the following measure, but I am not getting the results expected:

Revenue New Customers Delta  = 
VAR a = ADDCOLUMNS( Sales, "Customer" , Sales[Customer] , "Delta" , [Delta LTM],  "New Customers" , [New Customers LTM])
RETURN 
    SUMX(FILTER(a, [New Customers] = "Yes") , [Delta])

Kind regards,

Jan-Willem Beekhuis

[sturlaws · Accepted solution]

Hi, @Beekhuis 

I appriciate the well formulated post, along with the formatted dax code. If you also had included a representative data sample, it would have been a text book post.

I have created a dummy sample set which was not really suited for this problem, so bear with me if the following does not work.

First, you need to change your [Revenue LTM previous month]-measure to this:

Revenue LTM previous month = 
IF(not(ISBLANK(LASTDATE ( DATEADD ( vDate[date]; -13; MONTH ))));
CALCULATE (
    SUM ( Sales[Revenue] );
    DATESBETWEEN (
        vDate[date];
        LASTDATE ( DATEADD ( vDate[date]; -13; MONTH ) );
        LASTDATE ( DATEADD ( vDate[date]; -1; MONTH ) )
    )
);BLANK())

The reason for this is the somewhat unexpected behaviour of  DATESBETWEEN and LASTDATE. LASTDATE will return BLANK if the argument is less than the minimum value of the date column in question. And if the first argument of DATESBETWEEN is BLANK, it will use the earliest date of the date column in question as argument. If the second argument is BLANK, it will use the latest date of the date column as argument. The result of the original measure was on the first date periode in a chart or table, the measure would sum the revenue for the whole sales table.

Next, you should change up the [New Customers LTM]-measure like this:

New Customers LTM = IF(not(ISBLANK([Revenue LTM])) && ISBLANK([Revenue LTM previous month]) ; 1 ; 0 )

and [Revenue New Customers Delta]-measure:

Revenue New Customers Delta = 
VAR a = ADDCOLUMNS( VALUES(Sales[CustomerID]); "Delta" ; [Delta LTM];  "New Customers" ; [New Customers LTM])
RETURN 
    SUMX(FILTER(a; [New Customers] = 1) ; [Delta])

cheers,
Sturla

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.

[Beekhuis]

Hi @sturlaws ,

Thanks for your help. It works!!! Was the problem with the dates or how I did the IF statement with a "Yes/No".

Regards,

Jan-Willem

[sturlaws]

I think the first part of your if-check was the problem

[Revenue LTM] && ISBLANK([Revenue LTM previous month])

 because [Revenue LTM] does not return true/false

[Beekhuis]

@sturlaws ,

Last question, i'm also trying to count these new customers. I'm not getting it to work. 

This is my measure

Count New Customers = 
VAR a = ADDCOLUMNS( VALUES(Sales[CustomerID]); "Count" ; 1 ;  "New Customers" ; [New Customers LTM])
RETURN 
    SUMX(FILTER(a; [New Customers] = 1) ; [Count])

[sturlaws]

Number of new customers =
COUNTROWS (
    FILTER (
        ADDCOLUMNS ( VALUES ( Sales[CustomerID] ); "new"; [New Customers LTM] );
        [new] = 1
    )
)