Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to Show Last 4 Week Data dynamically

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 .....

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.