Distinct Count Based on Week Number Before Selected Week

[hani1711]

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?

[Anonymous]

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.

[FreemanZ]

hi @hani1711 ,

try like:

TotalCust = 

CALCULATE(

DISTINCTCOUNT('CustomerID'),

WeekofYear = MAX(WeekofYear) - 5

)

[Ritaf1983]

Hi @hani1711 

Please refer to the linked video:
https://www.youtube.com/watch?v=0enQCIWCSUE

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

could you pls provide some sample data and expected output?

[hani1711]

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 reason  

[ryan_mayu]

max week is 47 and 004 is in week 44, why not count 004 for last 6 weeks?

[hani1711]

because 004 isn't in week 47, the customer should be in the week 47 first before checking last 6 week

[ryan_mayu]

for the middle pivot table. if not in week 47, then even it exists in last 6 weeks, we don't want that. Why we need to check last 6 weeks?