How to Show Last 4 Week Data dynamically

[Anonymous]

HI,
I have Loaded Data Into PowerBI Which Already has Week Column present. I have created a Slicer for Location and Week , Where we can select Location and week number and my objective is to show Total of Sales handled in the week in the last 4 week before present week.
Example currently if Week 15 is selected, so column chart should show Week only 11,12,13,14 & Week 15 data.
If Week 3 is selected it should show Wk 1,2 & Wk 3 data. So dynamically it should change.

Even though i have tried creating Measure by learning online and through these forums like ->

Measure = CALCULATE(SUM('Data'[Teu]),FILTER('Data', DATEDIFF(Data[Week],TODAY() , WEEK )<5))

But Not able to Proceed pls Help.

These Column are Present In Data Table , Here Sales = TEU.... Kindly Help .....

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a separate week table and put the week field in the slicer. The week table is no related with the main table.

Measure = CALCULATE(SUM(Data[Teu]),FILTER(Data,[Week]<=SELECTEDVALUE(Week[Week])&&[Week]>=SELECTEDVALUE(Week[Week])-3))

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , Create a week rank column in date table

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

lasy 5 week based on selection

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

lasy 5 week based on today

Last Week today =
var _st = today() +-1*WEEKDAY(today(),2)+1 -7 -35
var _end =today()+ 7-1*WEEKDAY(today(),2) -7
return
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end )) //use all('Date') if need in filter