Forum Discussion

Kat2014's avatar
Kat2014
New Member
2 years ago
Solved

Weekly comparison YOY

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’...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.