Forum Discussion

NandhuE31's avatar
NandhuE31
Frequent Visitor
6 years ago
Solved

Distinct count for Consecutive last 3 months

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 ou...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    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.

     

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

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!