cancel
Showing results for
Did you mean:
Post Partisan

## 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,

1 ACCEPTED SOLUTION
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

5 REPLIES 5
Community Support

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.

Post Partisan

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,

Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Post Partisan

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,

Post Partisan

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,

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors