Week on Week comparison in a Clustered Column Chart

[rsbin]

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,

[v-xicai]

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]

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,

[mahoneypat · Accepted solution]

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]

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,

[rsbin]

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,