How to Exclude specific customers for relative dates and count residuals ?

[Anonymous]

Hi all,

I have a dataset as below.

CustomerNo	Category	InvoiceDate
000001	A	1.05.2020
000002	B	15.05.2020
000003	A	22.05.2020
000004	A	10.06.2020
000005	A	15.06.2020
000006	B	18.06.2020
000007	A	22.06.2020
000005	A	7.07.2020
000009	A	30.07.2020
000010	A	10.08.2020
000007	A	20.08.2020
000003	B	10.09.2020
000013	A	12.09.2020
000004	A	13.09.2020
000001	A	14.09.2020

As you can see there are customers, category and Invoice date fields. Invoice date means the customers transaction date. It might be more tane one invoice date for one Customer.
My requierment is the number of customers who have transaction within the last 6 months but not in the last 1 month
I want to exclude the customers from dataset which one have a transaction in last 1 month then distinct count others.
In my example these customers are 
"000007"
"000003"
"000013"
"000004"
"000001"
after i eliminated these customers from my dataset the final view is like that;

CustomerNo	Category	InvoiceDate
000002	B	15.05.2020
000005	A	15.06.2020
000006	B	18.06.2020
000005	A	7.07.2020
000009	A	30.07.2020
000010	A	10.08.2020

Then i can make the calculation like that,

Metric = CALCULATE (
    DISTINCTCOUNT ( 'Table'[CustomerNo] ),
    FILTER (
        'Table',
        'Table'[Category] = "A"
            && 'Table'[InvoiceDate]
                > TODAY () - 90))

In my example this metric should be return 3.
I need help for first part. I don't have any idea how can i filter the customers and summarized the dataset like that.
If you have another solution like don't summarized and just create a metric that is fine, it doesn't matter.
Any solution is suitable.

[Greg_Deckler · Accepted solution]

@Anonymous Try something like this:

Measure = 
  VAR __LastMonth = DISTINCT(SELECTCOLUMNS(FILTER('Table',[InvoiceDate]<=TODAY() && [InvoiceDate]>=DATE(YEAR(EOMONTH(TODAY(),-1)),MONTH(EOMONTH(TODAY(),-1)),DAY(TODAY()),"Customer",[Customer]))
  VAR __LastSixMonths = DISTINCT(SELECTCOLUMNS(FILTER('Table',[InvoiceDate]<=TODAY() && [InvoiceDate]>=DATE(YEAR(EOMONTH(TODAY(),-6)),MONTH(EOMONTH(TODAY(),-6)),DAY(TODAY()),"Customer",[Customer]))
  VAR __FinalTable = EXCEPT(__LastSixMonths,__LastMonth)
RETURN
  COUNTROWS(__FinalTable)

[AiolosZhao · Accepted solution]

Hi @Anonymous ,

Hope the below measure is what you want:

Measure 15 = CALCULATE(DISTINCTCOUNT('Table (4)'[CustomerNo]),FILTER('Table (4)','Table (4)'[Category] = "A")) - CALCULATE(DISTINCTCOUNT('Table (4)'[CustomerNo]),FILTER('Table (4)','Table (4)'[InvoiceDate] <= DATE(YEAR(NOW()),MONTH(NOW()) - 6,DAY(NOW())) || 'Table (4)'[InvoiceDate] >= DATE(YEAR(NOW()),MONTH(NOW()) - 1,DAY(NOW()))))

Aiolos Zhao

[Greg_Deckler · Accepted solution]

@Anonymous Try something like this:

Measure = 
  VAR __LastMonth = DISTINCT(SELECTCOLUMNS(FILTER('Table',[InvoiceDate]<=TODAY() && [InvoiceDate]>=DATE(YEAR(EOMONTH(TODAY(),-1)),MONTH(EOMONTH(TODAY(),-1)),DAY(TODAY()),"Customer",[Customer]))
  VAR __LastSixMonths = DISTINCT(SELECTCOLUMNS(FILTER('Table',[InvoiceDate]<=TODAY() && [InvoiceDate]>=DATE(YEAR(EOMONTH(TODAY(),-6)),MONTH(EOMONTH(TODAY(),-6)),DAY(TODAY()),"Customer",[Customer]))
  VAR __FinalTable = EXCEPT(__LastSixMonths,__LastMonth)
RETURN
  COUNTROWS(__FinalTable)

[amitchandak]

@Anonymous ,  Join invoice date with date table have measures like

Rolling 6 Sales = CALCULATE(Count(Table[CustomerNo]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-6,MONTH))

MTD = calculate(Count(Table[CustomerNo]),datesmtd('Date'[Date]))

Lost Customer  = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([MTD]) && not(ISBLANK([Rolling 3 Sales])) , 1,BLANK()))

Refer

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.