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 nameregistered date
Outlet1outlet name 101-01-2019
Outlet 2outlet name 201-01-2019
Outlet3outlet name 301-01-2019

 

Transaction Table:

outlet_code Transation date
Outlet101-12-2019
Outlet 201-01-2019
Outlet301-01-2019
Outlet101-01-2020
Outlet 201-02-2020
Outlet101-02-2020
Outlet 201-03-2020
Outlet 101-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 

 

 

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.

     

2 Replies

  • AnkitBI's avatar
    AnkitBI
    Solution Sage
    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
    Community 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!