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

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

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,