Year Over Year - Monday to Monday with Leap Year

[rwittmann08]

Hello,

Our organization is trying to figure out a way to calculate year over year calculations, but to the actual day.  For example...

Monday, 2/17/2020 vs Monday, 2/18/2019

The added complexity is with leap year.  For example...

Sunday, 3/1/2020 vs Sunday, 3/3/2019

We are trying to calculate simple equations (Sum of Sales, Sum of Quantity, etc.) and would appreciate help in this equation.

Any help would be great!

[v-frfei-msft]

Hi @rwittmann08 ,

Here I created a date table as below firstly.

Table = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2020, 12, 31 ) ),
    "Weeknum", WEEKNUM ( [Date], 2 ),
    "Weekday", WEEKDAY ( [Date], 2 ),
    "Year", YEAR ( [Date] )
)

After that, create a measure as below to work on it.

previous year = 
VAR maxyear =
    MAX ( 'Table'[Year] ) - 1
VAR weekd =
    MAX ( 'Table'[Weekday] )
VAR weekn =
    MAX ( 'Table'[Weeknum] )
RETURN
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = maxyear
                && 'Table'[Weekday] = weekd
                && 'Table'[Weeknum] = weekn
        )
    )

For more details, please check the pbix as attached.

[rwittmann08]

Hi @v-frfei-msft,

That works!  Thank you!  I can confirm the day shifts happen as expected with this.  Monday to Monday. 

Is it possible to create a measure off of this so visualizations can be used.  Please see below...

[amitchandak · Accepted solution]

@rwittmann08 

To me, the above solution seems like a measure only. It is created as a column.

Typically same weekday last year is 364 days behind, so you can also use (Example)

Week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))

Make sure you have Date table.

[rwittmann08]

Hi @amitchandak,

Thank you for sharing this as this is working perfectly!

Ryan 

[amitchandak]

Please mark the solution that worked for you.

[rwittmann08]

Thank you for responding @v-frfei-msft! I'll try this out!

[Pragati11]

Hi @rwittmann08 ,

I think you can use SAMEPERIODLASTYEAR dax in your case.

https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

Thanks,

Pragati

[rwittmann08]

Spoiler

Hello @Pragati11,

Thank you for responding.  I have looked into this; however, it does the date equivalent to LY not the day equivalent.

Formula does: 2/18/2020 vs 2/18/2019

I need: 2/18/2020 vs 2/19/2019.  Once leap year occurs I will need... 3/1/2020 to 3/3/2019.