Forum Discussion

jwisrael's avatar
jwisrael
Frequent Visitor
4 years ago

New Customer Count TTM DAX

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

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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

     

  • jwisrael's avatar
    jwisrael
    Frequent Visitor
    4 years ago

    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. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.