New Customer Count TTM DAX

[jwisrael]

Hello, I'm look for some direction on a dax issue i'm having. I need to create a New Customer TTM (Trailing Twelve Month Measure) thats dynamic. New Customers are customers that exist within the last 12 months but not the 12 months before that.  I went with a 

calculated table method but i'm running into issues where the numbers are incorect on a line graph, i'm guess it's because theres no date table relationship with my calculated table. Any direction or help would be great. Below is what I have so far. 

New Customer TTM =

VAR CustomerInvoiceCountbyBucket =

CALCULATETABLE (

ADDCOLUMNS (

VALUES ( 'P21&IBS Data'[CustomerID] ),

"lastYearInvoiceCount", CALCULATE(DISTINCTCOUNT('P21&IBS Data'[InvoiceKey]),DATESINPERIOD('Date Table'[DateValue],CALCULATE(LASTDATE('Date Table'[DateValue]),'Date Table'[CurrentMonthOffset]<=-1),-12,month),all('Date Table')),

"lastTwoInvoiceCount", calculate(CALCULATE(DISTINCTCOUNT('P21&IBS Data'[InvoiceKey]),DATESINPERIOD('Date Table'[DateValue],CALCULATE(LASTDATE('Date Table'[DateValue]),'Date Table'[CurrentMonthOffset]<=-1),-12,month),all('Date Table')), SAMEPERIODLASTYEAR('Date Table'[DateValue])

)))

VAR newCustomers =

FILTER (

CustomerInvoiceCountbyBucket,

[lastTwoInvoiceCount] = blank() && [lastYearInvoiceCount] > 0

)

VAR Result =

COUNTROWS ( newCustomers )

RETURN

Result

[Anonymous]

Hi @jwisrael ,

Why do you count the InvoiceKey? Please share some dummy data and expected results to me.

Check this link might helps you.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jwisrael]

Thanks for simplifying it, I'm still running into the same date issue though. Let me know if you have any tips or solutions. This DAX might not even be the right solution, its just what came to mind. 

[AlexisOlson]

Your date logic is fairly complicated. I think this might be a bit easier to debug:

New Customer TTM =
VAR CurrDate = MAX ( 'Date Table'[DateValue] )
VAR CustomerInvoiceCountbyBucket =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'P21&IBS Data'[CustomerID] ),
            "lastYearInvoiceCount",
                CALCULATE (
                    DISTINCTCOUNT ( 'P21&IBS Data'[InvoiceKey] ),
                    DATESINPERIOD ( 'Date Table'[DateValue], CurrDate, -1, YEAR )
                ),
            "lastTwoInvoiceCount",
                CALCULATE (
                    DISTINCTCOUNT ( 'P21&IBS Data'[InvoiceKey] ),
                    DATEADD (
                        DATESINPERIOD ( 'Date Table'[DateValue], CurrDate, -1, YEAR ),
                        -1,
                        YEAR
                    )
                )
        )
    )
VAR newCustomers =
    FILTER (
        CustomerInvoiceCountbyBucket,
        ISBLANK ( [lastTwoInvoiceCount] ) && [lastYearInvoiceCount] > 0
    )
VAR Result = COUNTROWS ( newCustomers )
RETURN
    Result