Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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")

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!