Distinct Count over custom week range

[Anonymous]

I have some code that I need to adapt to get a Distinct Count of StoreID

At the moment this code is summing the StoreID Field

No. Of Stores Registered Sale L4W =
VAR CurrentWeek = SELECTEDVALUE( Dates[Sequential Week Number] )
VAR CurrentWeekMinus4 = CurrentWeek - 3
RETURN
SUMX(FILTER(ALL(Dates), Dates[Sequential Week Number] <= CurrentWeek && Dates[Sequential Week Number] >= CurrentWeekMinus4),[No. Of Stores Registered Sale])

Any help appreciated, can't see what to do!

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

>> to get a Distinct Count of StoreID

could you please share some sample data and its expected outcome? 

Best Regards,

Community Support Team _Tang

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

[Anonymous]

I don't think this would give me a Distinct Count of StoreIDs

[amitchandak]

@Anonymous , use week rank on yyyyww in a separate date/week table 

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Last 3 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 3 weeks =
var _max1 = maxx(allselected('Table'), 'Table'[date])
var _max = maxx(filter(all(date), date[date] = _max1), week[Rank])
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=_max-3 && 'Date'[Week Rank]<=_max))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8