How to create Bar chart comparing 'This Week', 'Last Week', and 'This Week Last Year' data

jimbob741

I’d like to give you a sample as below. The .pbix file has been upload here. I assume we have a table like below.

We can first create a calendar table and create two columns with following formula. Create a relationship between these two tables with Date key.

Calendar = CALENDAR ( "1/1/2015", "12/31/2016" )

WeekNum = WEEKNUM ( 'Calendar'[Date] )

Year = YEAR ( 'Calendar'[Date] )

In original table, also create a WeekNum column.

WeekNum = RELATED ( 'Calendar'[WeekNum] )

Then we only need to create two measures to get 'Last Week' and 'This Week Last Year' data.

Last Week = 
VAR CurrentWeekNum =
    MAX ( 'Calendar'[WeekNum] )
VAR CurrentYear =
    MAX ( 'Calendar'[Year] )
RETURN
    (
        CALCULATE (
            SUM ( Table1[Users] ),
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year] = CurrentYear
                    && 'Calendar'[WeekNum]
                    = CurrentWeekNum - 1
            )
        )
)

This Week Last Year = 
VAR CurrentWeekNum =
    MAX ( 'Calendar'[WeekNum] )
VAR CurrentYear =
    MAX ( 'Calendar'[Year] )
RETURN
    (
        CALCULATE (
            SUM ( Table1[Users] ),
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year]
                    = CurrentYear - 1
                    && [WeekNum] = CurrentWeekNum
            )
        )
    )

Drag two slicers for Year & WeekNum and a clustered bar chart into canvas.

Best Regards,

Herbert