Forum Discussion

rsbin's avatar
rsbin
Community Champion
6 years ago
Solved

Week on Week comparison in a Clustered Column Chart

Good Afternoon,

 

I am using a Clustered Column Chart to create a Week by Week comparison for 2020 vs 2019.  Dataset starts at Jan 1, 2019

I have WeekNumber on the X-axis and Year in my Legend.  I would only like to show a rolling 13 weeks time series.

 

So far I can show the last 13 weeks for 2020 by Filtering on Top 13 of Year-WeekNum.

I have also created a Week Rank and filtered on the Top 13 weeks (kudos to amitchandak for the idea on Rank), thinking I can somehow work with this.

I am drawing blanks on how to grab the same 13 weeks for the Prior Year (2019) ???

Thoughts and suggestions are much appreciated.

 

Best Regards to All,

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Assuming you have a Date table with a WeekNumber column, you should be able to use a measure like this that should give nonblank/shown values for the last 13 weeks.

     

    NewMeasure =
VAR thisweek =
    WEEKNUM ( TODAY () )
RETURN
    CALCULATE (
        [Your Measure],
        KEEPFILTERS (
            FILTER (
                ALL ( 'Date'[WeekNumber] ),
                'Date'[WeekNumber] <= thisweek
                    && 'Date'[WeekNumber] >= thisweek - 12
            )
        )
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Assuming you have a Date table with a WeekNumber column, you should be able to use a measure like this that should give nonblank/shown values for the last 13 weeks.

     

    NewMeasure =
VAR thisweek =
    WEEKNUM ( TODAY () )
RETURN
    CALCULATE (
        [Your Measure],
        KEEPFILTERS (
            FILTER (
                ALL ( 'Date'[WeekNumber] ),
                'Date'[WeekNumber] <= thisweek
                    && 'Date'[WeekNumber] >= thisweek - 12
            )
        )
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • rsbin's avatar
      rsbin
      Community Champion
      6 years ago

      Good Evening mahoneypat 

       

      Thanks for the reply and suggested solution.  I will give it a shot in the morning and let you know how I make out.

       

      Thanks again and Best Regards,

    • rsbin's avatar
      rsbin
      Community Champion
      6 years ago

      Hello mahoneypat 

       

      I have implemented your solution and thus far appears to be working exactly as I hoped.  Still need to do a bit more testing and validation.  But wanted to send along my thanks, appreciation and of course Kudos!

       

      Best Regards,

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi rsbin ,

     

    You may create Week of Year column , and two rolling 13 week measures like DAX below.

     

     

     

    Column:

Week of Year = WEEKNUM(Table1[Date])




Measure:


Rolling 13 Weeks Current Year =
VAR i= SELECTEDVALUE(Table1[Week])
RETURN
CALCULATE (SUM ( Table1[Sales] ),FILTER ( ALLSELECTED(Table1), YEAR(Table1[Date])= YEAR(MAX(Table1[Date]))&&Table1[Week] <= i + 12 && Table1[Week] >= i ) )


Rolling 13 Weeks Previous Year =
VAR i= SELECTEDVALUE(Table1[Week])
RETURN
CALCULATE (SUM ( Table1[Sales] ),FILTER ( ALLSELECTED(Table1), YEAR(Table1[Date])= YEAR(MAX(Table1[Date]))-1&&Table1[Week] <= i + 12 && Table1[Week] >= i ))

     

     

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

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

    • rsbin's avatar
      rsbin
      Community Champion
      6 years ago

      Hello v-xicai 

       

      Thank you so much for sending an alternative solution.  Although I think your Measures will work, I think mahoneypat solution is slightly better suited for my clustered column chart.

      Thanks again and kudos to you as well!

       

      Kind Regards,