CALCULATE UNIQUE COUNT FOR LAST 6 WEEKS USING DAX

[sid-poly]

I have a table that has start of week and also invoice numbers which can be carried forward to another week too

INVOICE NUM	START OF WEEK
123	2/2/2022
456	1/2/2022
789	31/1/2022
123	28/1/2022
101	8/2/2022
203	10/2/2022
789	10/2/2022
456	10/2/2022

Now, I want o to write a DAX that would give me the DistinctCount of All the Invoice Numbers for the last 6 Weeks. But it should distinct in every week and not overall. Let us say in the Above table 123 Invoice is a part of 2 different weeks, so the count should be 2, which means the DISTINCTCOUNT should happen on INVOICES every week and not overall.

I have written it as 

CALCULATE(DISTINCTCOUNT(INVOICE NUM),FILTER(TABLE,WEEKNUM(START OF WEEK) >= WEEKNUM(TODAY())-6 && WEEKNUM(START OF WEEK) <= WEEKNUM(TODAY())))

It is not doing the same as I don't get the same amount of number that I need

[V-lianl-msft · Accepted solution]

Hi @sid-poly ,

Try to create a measure like below:

Measure = calculate(countrows(DISTINCT(SELECTCOLUMNS('Table',"Start",'Table'[START OF WEEK],"Invoice",'Table'[INVOICE NUM]))),FILTER('Table',WEEKNUM('Table'[START OF WEEK]) >= WEEKNUM(TODAY())-6 && WEEKNUM('Table'[START OF WEEK]) <= WEEKNUM(TODAY())))

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

[Greg_Deckler]

@sid-poly Maybe try:

COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(TABLE,WEEKNUM(START OF WEEK) >= WEEKNUM(TODAY())-6 && WEEKNUM(START OF WEEK) <= WEEKNUM(TODAY()),"__Invoice",[INVOICE NUM])))