Forum Discussion
Distinct Count Based on Week Number Before Selected Week
Hello,
I have table consist of CustomerID, TransactionDate, WeekofYear. I want to DISTINCTCOUNT the customer based on 6 weeks before current week and display it in column chart. So if the column chart 47 week should look from week 42.
My Measure like this:
TotalCust =
CALCULATE(
DISTINCTCOUNT('CustomerID'),
WeekofYear >= MAX(WeekofYear) - 6 &&
WeekofYear <= MAX(WeekofYear)
)
I have tried like that but di value is just DISTINCTCOUNT the week in the current state. Is there anything false in my dax?
8 Replies
could you pls provide some sample data and expected output?
hi ryan
here is the sample of data and what the expected value
Example:
1. For week 47 the value should be 3, because just the 3 customer in the last 6 week based on customer in week 47, cust 004 is excluded
2. For week 47 with the filter brand A the value should be 2, with the same reasonmax week is 47 and 004 is in week 44, why not count 004 for last 6 weeks?
Hi hani1711
Please refer to the linked video:
https://www.youtube.com/watch?v=0enQCIWCSUEIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi hani1711
Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
