Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic 13 weeks based on baseweek selection slicer

Hi All, I am new to Power BI.   Can anyone please help me on dynamic 13 week selection based on my base week slicer.   Eg, if i select 202013 then i need the output as 202001 to 202013   if i s...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Icey  I have used Lookupvalue and hasonevalue to get my requirement. 

     

    Below are the measures which i have used. 

     

    13 WKS = 

    CALCULATE(SUM('TM Trend'[Sat_Day_Test_Count]), DATESBETWEEN('calendar'[Reference_Date], [CUS_CY13W_Start], [CUS_CY_BaseDate]))
     
    CUS_CY13W_Start = LOOKUPVALUE('Calendar'[CUS_CY13W_StartDate], 'Calendar'[CUS_CY_Baseweek], [CUS_CY_BaseWeek_Selected])
     
    CUS_CY_BaseWeek_Selected = IF(HASONEVALUE('Weeklist'[Base Week]), VALUES('Weeklist'[Base Week]), BLANK())
amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , refer my blog on Week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

and these

Last 13 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
or
Last 13 period Sales =
Var _min = maxx(allselected('Date','Date'[Week Rank])
Var _max = maxx(allselected('Date','Date'[Week Rank]) -13
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))