Weekly comparison YOY

[Kat2014]

Hello all,

I’m new to Power BI and was wondering if someone could help me with this. I watched some videos on it and read a few things online, but it didn’t quite match what I’d like to achieve.  I’m not familiar with all the Power BI terms just yet so be nice haha.

I’ve got two spreadsheets containing the following information.

First spreadsheet

• Booking date
• Brand
• Revenue
• People travelling
• Travel date
• Salesperson

Second spreadsheet

• Booking date
• Revenue targets for each day for this FY and next FY that are based on different brands. Our FY runs from September to August, not sure if that’s relevant

I’d like to create a table and visual that will show the revenue per week per brand for the last 4 weeks (Mon-Sun) and the same dates last year and compare them to this year’s target.

For example, booking week 22.7.24 – 28.7.24 compared to 22.7.23 – 28.7.23, even though last year it wasn’t Mon-Sun. It should always show the last 4 weeks (Mon-Sun) when updating the data. I’m struggling with it as we don’t compare week numbers.

The X axis should show 4 bars reflecting the last 4 weeks Mon-Sun (8.7 - 14.7.24; 15.7. – 21.7.24; 22.7. – 28.7.24 and 29.7. 4.8.24, for example), a bar next to it with the same dates but for 2023 and a bar with the targets for this year. The Y axis should show the revenue.

Is that possible?

Any help is appreciated. If you can point me in the right direction that would be amazing. Thank you very much for your help in advance. I hope the above makes sense.

Thank you 😊

[v-zhengdxu-msft · Accepted solution]

Hi @Kat2014 

Here I create a set of sample:

Revenue Table:

Target Table:

Then add a calculated table:

Table 2 = {1,2,3,4}

add a calculated column in table 2:

weeks =
VAR _currentValue = 'Table 2'[Value]
VAR _vtable =
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Revenue'[Booking date] ), MAX ( 'Revenue'[Booking date] ) ),
        "_Weekday", WEEKDAY ( [Date], 2 ),
        "_WeekNum", WEEKNUM ( [Date], 2 )
    )
VAR _NowNumber =
    MAXX ( FILTER ( _vtable, [Date] = TODAY () ), [_WeekNum] )
RETURN
    MINX (
        FILTER (
            _vtable,
            YEAR ( [Date] ) = YEAR ( TODAY () )
                && [_WeekNum] = _NowNumber - _currentValue
        ),
        [Date]
    ) & "--"
        & MAXX (
            FILTER (
                _vtable,
                YEAR ( [Date] ) = YEAR ( TODAY () )
                    && [_WeekNum] = _NowNumber - _currentValue
            ),
            [Date]
        )

At last, add 3 measure:

2024_revenue =
VAR _currentValue =
    SELECTEDVALUE ( 'Table 2'[Value] )
VAR _vtable =
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Revenue'[Booking date] ), MAX ( 'Revenue'[Booking date] ) ),
        "_Weekday", WEEKDAY ( [Date], 2 ),
        "_WeekNum", WEEKNUM ( [Date], 2 )
    )
VAR _NowNumber =
    MAXX ( FILTER ( _vtable, [Date] = TODAY () ), [_WeekNum] )
VAR _mindate =
    MINX (
        FILTER (
            _vtable,
            YEAR ( [Date] ) = YEAR ( TODAY () )
                && [_WeekNum] = _NowNumber - _currentValue
        ),
        [Date]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            _vtable,
            YEAR ( [Date] ) = YEAR ( TODAY () )
                && [_WeekNum] = _NowNumber - _currentValue
        ),
        [Date]
    )
RETURN
    CALCULATE (
        SUM ( Revenue[Revenue] ),
        FILTER (
            ALLSELECTED ( Revenue ),
            'Revenue'[Booking date] >= _mindate
                && 'Revenue'[Booking date] <= _maxdate
        )
    )

2023_revenue =
VAR _currentValue =
    SELECTEDVALUE ( 'Table 2'[Value] )
VAR _vtable =
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Revenue'[Booking date] ), MAX ( 'Revenue'[Booking date] ) ),
        "_Weekday", WEEKDAY ( [Date], 2 ),
        "_WeekNum", WEEKNUM ( [Date], 2 )
    )
VAR _NowNumber =
    MAXX ( FILTER ( _vtable, [Date] = TODAY () ), [_WeekNum] )
VAR _mindate =
    MINX (
        FILTER (
            _vtable,
            YEAR ( [Date] ) = YEAR ( TODAY () )
                && [_WeekNum] = _NowNumber - _currentValue
        ),
        [Date]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            _vtable,
            YEAR ( [Date] ) = YEAR ( TODAY () )
                && [_WeekNum] = _NowNumber - _currentValue
        ),
        [Date]
    )
RETURN
    CALCULATE (
        SUM ( Revenue[Revenue] ),
        FILTER (
            ALLSELECTED ( Revenue ),
            'Revenue'[Booking date]
                >= DATE ( YEAR ( _mindate ) - 1, MONTH ( _mindate ), DAY ( _mindate ) )
                && 'Revenue'[Booking date]
                    <= DATE ( YEAR ( _maxdate ) - 1, MONTH ( _maxdate ), DAY ( _maxdate ) )
        )
    )

2024_target =
VAR _currentValue =
    SELECTEDVALUE ( 'Table 2'[Value] )
VAR _vtable =
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Revenue'[Booking date] ), MAX ( 'Revenue'[Booking date] ) ),
        "_Weekday", WEEKDAY ( [Date], 2 ),
        "_WeekNum", WEEKNUM ( [Date], 2 )
    )
VAR _NowNumber =
    MAXX ( FILTER ( _vtable, [Date] = TODAY () ), [_WeekNum] )
VAR _mindate =
    MINX (
        FILTER (
            _vtable,
            YEAR ( [Date] ) = YEAR ( TODAY () )
                && [_WeekNum] = _NowNumber - _currentValue
        ),
        [Date]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            _vtable,
            YEAR ( [Date] ) = YEAR ( TODAY () )
                && [_WeekNum] = _NowNumber - _currentValue
        ),
        [Date]
    )
RETURN
    CALCULATE (
        SUM ( Target[Revenue target] ),
        FILTER (
            ALLSELECTED ( 'Target' ),
            'Target'[Booking date] >= _mindate
                && 'Target'[Booking date] <= _maxdate
        )
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.