Change YTD with Week Slicer

[Anonymous]

Hi,

I'd like to show the latest week sales and YTD sales, with YTD calculating from the start of the year to the selected week on a slicer.

I've selected week 5 on my slicer so i want YTD to show the sum of sales from WK1 to 5. I've written several measures for YTD but they all return just the sales for WK5 and not YTD to week 5:

test3 = CALCULATE([sales],DATESYTD(DateTable[CalendarDate],"30/03")) ... this brings back just WK5 sales

test4 = TOTALYTD([sales],DateTable[CalendarDate]) ... this brings back just WK5 sales

I have data for week 6 onwards so i cant simply remove the filters without data reading beyond WK5.

[Anonymous]

Hi @amitchandak 

Out of interest, why didnt the 'filter(all(...)' remove the slicer filter and therefore sum a YTD for the full year and not just weeks 1 to 5?

[amitchandak · Accepted solution]

@Anonymous , Not very clear but with week year and week number you can have it. But use a separate table

YTD Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Number]<=max('Date'[Week Number]) && 'Date'[Week Year]= max('Date'[Week Year])))

YTD till last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Number]<=max('Date'[Week Number]) -1 && 'Date'[Week Year]= max('Date'[Week Year])))
LYD Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Number]=max('Date'[Week Number]) && 'Date'[Week Year]= max('Date'[Week Year])-1 ))

Week column need in Date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 // Monday to sunday week
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)

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

[Anonymous]

Thanks, this solution works perfectly!