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

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,