Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter last x Weeks

Hi,

I want to bring the last 6 weeks data in my trend chart.

I tried the relative date but unable to get weeks in Date attribute.
Actually i'm calculating week in text form from the date I'm calculating week like;

in current week, we have saturday on 19th December so my week name is like WE 19 Dec 2020 and for sorting these weeks i'm using WeekNumber.



Suggest me a solution how i can i get the last 6 weeks for selected months.

Thanks,

Gaurav




Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    Try this:

     

    1. Create a What-if parameter.

     

    2. Create a measure and put it into "Filters on this visual" field of your table visual, and then set as "is not blank".

     

    Last n weeks = 
VAR n_ = [N Value]
VAR MaxWeek =
    CALCULATE ( MAX ( Dates[Week_Sorting] ), ALLSELECTED ( Dates ) )
VAR ThisWeek_ =
    MAX ( Dates[Week_Sorting] )
RETURN
    IF ( ThisWeek_ > MaxWeek - n_ && ThisWeek_ <= MaxWeek, 1 )

     

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous - So a lot of times how you do this is to create a calculated column in your table that basically is WEEKNUM(TODAY()) - WEEKNUM([Date]) and then you can filter by the current year and this column <=6

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Greg_Deckler 

      I'm also providing a month filter here. So if user select Month Jun and Jul today(in Dec) so i want to filter last 6 weeks(4 from Jul and last 2 from Jun).

      Thanks
      Gaurav

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Anonymous - Sounds like you want to do it in a measure then instead of a calculated column.

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Try this:

     

    1. Create a What-if parameter.

     

    2. Create a measure and put it into "Filters on this visual" field of your table visual, and then set as "is not blank".

     

    Last n weeks = 
VAR n_ = [N Value]
VAR MaxWeek =
    CALCULATE ( MAX ( Dates[Week_Sorting] ), ALLSELECTED ( Dates ) )
VAR ThisWeek_ =
    MAX ( Dates[Week_Sorting] )
RETURN
    IF ( ThisWeek_ > MaxWeek - n_ && ThisWeek_ <= MaxWeek, 1 )

     

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.