Distinct count for Consecutive last 3 months

[NandhuE31]

Hi, 

I Need to create a calculated column for distinct count of outlets having transaction in last 3 months. 

Registered Outlets:

outlet_code	outlet name	registered date
Outlet1	outlet name 1	01-01-2019
Outlet 2	outlet name 2	01-01-2019
Outlet3	outlet name 3	01-01-2019

Transaction Table:

outlet_code	Transation date
Outlet1	01-12-2019
Outlet 2	01-01-2019
Outlet3	01-01-2019
Outlet1	01-01-2020
Outlet 2	01-02-2020
Outlet1	01-02-2020
Outlet 2	01-03-2020
Outlet 1	01-03-2020

In the example above, For consecutively billed in last 3 months [in months n-2 AND n-1 AND n], result should be 
Consecutively billed in L3M= 1 outlet.  (Outlet 1 is billed for period Jan - Mar 2020)

Also, help me with calculating Billed atleast once in last 3 months-> distinct count of outlets billed atleast once in last three months [in months n-2 OR n-1 OR n]

Example output=3 

[v-kelly-msft]

Hi @NandhuE31 ，

You need 2 measures as below:

Consecutively billed in L3M = 
var __Trans_2 = SUMMARIZE(filter('Transaction Table',format('Transaction Table'[Transation date],"yyyymm") = format(edate(MAX('Transaction Table'[Transation date]),-2),"yyyymm")),'Transaction Table'[outlet_code ])
var __Trans_1 = SUMMARIZE(filter('Transaction Table',format('Transaction Table'[Transation date],"yyyymm") = format(edate(MAX('Transaction Table'[Transation date]),-1),"yyyymm")),'Transaction Table'[outlet_code ])
var __Trans = SUMMARIZE(filter('Transaction Table',format('Transaction Table'[Transation date],"yyyymm") = format(TODAY(),"yyyymm")),'Transaction Table'[outlet_code ])
var __Combined = intersect(INTERSECT(__Trans_2,__Trans_1),__Trans)
return
countrows(__Combined)

BilledAtleastOnce = SUMX(VALUES('Transaction Table'[outlet_code ]),CALCULATE(
var __MinDate = date(Year(edate(MAX('Transaction Table'[Transation date]),-2)),month(edate(MAX('Transaction Table'[Transation date]),-2)),1)
var __MaxDate = EDATE(MAX('Transaction Table'[Transation date]),0) 

var __Trans_2= SUMMARIZE(filter('Transaction Table','Transaction Table'[Transation date] >= __MinDate && 'Transaction Table'[Transation date] <=__MaxDate),'Transaction Table'[outlet_code ])
return
COUNTROWS(__Trans_2)))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AnkitBI · Accepted solution]

Hi @NandhuE31 

First thing, always check your sample data before posting. Like 'Outlet 1' at some rows is having space while in others not. Wasted my half an hour due to this.

For your queries, please try below formulas.

Consecutively billed in L3M = 
var __Trans_2 = SUMMARIZE(filter('Table1',format('Table1'[Transation date],"yyyymm") = format(edate(TODAY(),-2),"yyyymm")),Table1[outlet_code ])
var __Trans_1 = SUMMARIZE(filter('Table1',format('Table1'[Transation date],"yyyymm") = format(edate(TODAY(),-1),"yyyymm")),Table1[outlet_code ])
var __Trans = SUMMARIZE(filter('Table1',format('Table1'[Transation date],"yyyymm") = format(TODAY(),"yyyymm")),Table1[outlet_code ])
var __Combined = intersect(INTERSECT(__Trans_2,__Trans_1),__Trans)
return
countrows(__Combined)

BilledAtleastOnce = 

var __MinDate = date(Year(edate(today(),-2)),month(edate(today(),-2)),1)
var __MaxDate = EDATE(Today(),0) 

var __Trans_2= SUMMARIZE(filter('Table1','Table1'[Transation date] >= __MinDate && 'Table1'[Transation date] <=__MaxDate),Table1[outlet_code ])

return
countrows(__Trans_2)

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.