Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
Hi,
You may download my PBI file from here.
Hope this helps.
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
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)))
Thanks,Let me give a try
Thanks a lot for your time and efforts Mate!!!!It Worked
User | Count |
---|---|
13 | |
4 | |
2 | |
1 | |
1 |
User | Count |
---|---|
22 | |
3 | |
3 | |
2 | |
2 |