topic New Customer Count TTM DAX in DAX Commands and Tips

New Customer Count TTM DAX

jwisrael — Thu, 18 Nov 2021 21:20:20 GMT

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

Re: New Customer Count TTM DAX

AlexisOlson — Thu, 18 Nov 2021 23:13:42 GMT

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

Re: New Customer Count TTM DAX

jwisrael — Fri, 19 Nov 2021 03:23:56 GMT

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.

Re: New Customer Count TTM DAX

Anonymous — Thu, 25 Nov 2021 07:57:52 GMT

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.