Select Week Range from dropdown and Update YTD Figures to that week ending date

[Anonymous]

I have a dropdown to select a weekly range, the information updates on the visual for WTD (because I have edited the format to edit interactions for only the WTD visuals)

The YTD visuals are not touched by the weekly range selection, these numbers reflect the figures as of the last week range loaded in the data.

Example, I have year 2020, loaded 40 weeks, I can see the view of each week by selecting from the drop down week 30 or week 38, The YTD will show results as of week 40, because it's the last week range in the data.

How Do I insert a formula that shows results YTD as of the weekly range selected in the dropdown?

the formula for the weekly range is as follows:

Weekly range =

var startdateperweek=CALCULATE(MIN('Views '[Day]),FILTER('Views ','Views '[Day].[Year]=EARLIER('Views '[Day].[Year])&&'Views '[WeekNum]=EARLIER('Views '[WeekNum])))

var enddateperweek= CALCULATE(MAX('Views '[Day]),FILTER('Views ','Views '[Day].[Year]=EARLIER('Views '[Day].[Year])&&'Views '[WeekNum]=EARLIER('Views '[WeekNum])))

return

FORMAT(startdateperweek,"mm/d/yyyy")&" - " &FORMAT(enddateperweek,"mm/d/yyyy")

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

First create a dim week table as below：

Week table = VALUES('Views'[_Weeknum])

Then create a measure as below:

Weekly range = 
var startdateperweek=CALCULATE(MIN('Views'[Day]),FILTER(Views,YEAR('Views'[Day])=YEAR(MAX('Views'[Day]))&&'Views'[_Weeknum]=SELECTEDVALUE('Week table'[_Weeknum])))
var enddateperweek= CALCULATE(MAX('Views'[Day]),FILTER(Views,YEAR('Views'[Day])=YEAR(MAX('Views'[Day]))&&'Views'[_Weeknum]=SELECTEDVALUE('Week table'[_Weeknum])))
Return
FORMAT(startdateperweek,"mm/d/yyyy")&" - " &FORMAT(enddateperweek,"mm/d/yyyy")

And you will see:

If you want all rows to show the weekly range,you could use below dax expression:

Weekly range = 
var startdateperweek=CALCULATE(MIN('Views'[Day]),FILTER(ALL(Views),YEAR('Views'[Day])=YEAR(MAX('Views'[Day]))&&'Views'[_Weeknum]=SELECTEDVALUE('Week table'[_Weeknum])))
var enddateperweek= CALCULATE(MAX('Views'[Day]),FILTER(ALL(Views),YEAR('Views'[Day])=YEAR(MAX('Views'[Day]))&&'Views'[_Weeknum]=SELECTEDVALUE('Week table'[_Weeknum])))
Return
FORMAT(startdateperweek,"mm/d/yyyy")&" - " &FORMAT(enddateperweek,"mm/d/yyyy")

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , Make sure , you have week and year in new table

Try meaures like

YTD Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Number]<=max('Date'[Week Number]) && '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 ))

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

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

few columns which you can have , some are based on date

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

Week Year = minx(filter('Date',[Year] =earlier([Year])),[Week Start date]) // based on start or end date of week

You can refer-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