New Customer Analysis

DAX DAX Calculations
2

Hi @Hesham,

There was a similair requirement not long ago you can find that thread here:

Also be sure to check out Sam’s video on this topic.
https://forum.enterprisedna.co/t/who-are-the-lost-customers-advanced-analytics-with-power-bi-dax/2969
.
I’ve added a MonthOffset to your Date table:

InsertMonthOffset = Table.AddColumn(AddFY, "MonthOffset", each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(DateTime.FixedLocalNow()))) + Date.Month(Date.From(DateTime.FixedLocalNow()))))

And expanded the date range in your calendar.

.
Next I created this measure:

New Customers v2 = 
VAR ThisMonth = LOOKUPVALUE( 'Date'[MonthOffset], 'Date'[MonthInCalendar], SELECTEDVALUE('Date'[MonthInCalendar]))

VAR PreviousCustomers =
    CALCULATETABLE ( VALUES ( Data[Customer] ),
        FILTER ( ALL( 'Date' ),
        'Date'[MonthOffset] <= ThisMonth -1
        )
    )
VAR NewCustomers =
    CALCULATETABLE ( VALUES ( Data[Customer] ),
        FILTER ( ALL ( 'Date' ),
        'Date'[MonthOffset] = ThisMonth
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( NewCustomers, PreviousCustomers ) )

.
And this is the result
image

I hope this is helpful, here’s the solution file:
eDNA - Sample New Customer.pbix (140.4 KB)

1 Like