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
I hope this is helpful, here’s the solution file:
eDNA - Sample New Customer.pbix (140.4 KB)