Nested Aggr For Distinct count of customers over rolling 12 month period

[Giridhar0715]

Hi Guys,

I am struggling with a scenrio,Where i am creating a trend chart for rolling 12 month,I need to consider Distinct count of customers for last 12 months (For April 2023 i.e. May 2022-April 2023 & March 2023 i.e. April 2022-March 2023 ) and also check if the number of distinct product against those customers to be >= 2.

Kindly find below given data and output required

Output required (Distinct Customers(Product is >=2))

For Feb-23 count is 1,As only Cust ID 6 has Distinct Product Count >=2

For Dec2022 the count should be 2,As Cust ID 6&10 got Distinct Product Count >=2

Measure for distinct customer is

Running Count For 12 Months =
Var start_date = Max(TrxTable[Month])
Var MAX = Max(TrxTable[Month])
var MINI = datevalue(Month(EDATE(MAX,-11)) & "/1/" &Year(EDATE(MAX,-11)))
return

CALCULATE(DISTINCTCOUNT(TrxTable[Cust ID]),FILTER(All(TrxTable),TrxTable[TrxDate]<=MAX && TrxTable[TrxDate]>=MINI))

I am currently struggling with Distinct Customers(Product count is >=2) where i would be requireing nested aggr

@Ashish_Mathur 

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Giridhar0715]

Could you please guide how i can add one more filter,Where i want to check if the customer got Product count >=2 this year and last year.

Eg:For April 2023 i.e. Check if the Product count is >=2 for individual customer within May 2022-April 2023

and also for the same customer  the Product count is >=2 for May 2021-April2022

[Ashish_Mathur]

Hi,

Try this measure

Measure = if([Customer count]=BLANK(),BLANK(),COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(Data[Cust ID]),DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-23),MAX('Calendar'[Date]))),Data[Cust ID],"ABCD",CALCULATE([Products bought],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-11),MAX('Calendar'[Date]))),"EFGH",CALCULATE([Products bought],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-23),edate(MAX('Calendar'[Date]),-12)))),[ABCD]>=2&&[EFGH]>=2)))

[Giridhar0715]

Thanks,Let me give a try

[Giridhar0715]

Thanks a lot for your time and efforts Mate!!!!It Worked