WTD sales for same week last year comparing for the same elapsed days this year

[Anonymous]

Hello,

I am struggling to get an accurate result because the sales last year WTD is taking the whole week instead of the day to date.

For example, I would like to compare WTD from 14 January until 18 January versus the same period last year.

Here is my formula for the LY WTD. This will take the full week last year instead of 14th to 18th.

TOTAL SALES LY WTD =
CALCULATE (
    SUM ( FACT_sales ),
    FILTER (
        ALL ( 'Date' ),
        YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )-1
        && MONTH( 'Date'[Date] ) = MONTH ( TODAY () )
        && WEEKNUM('Date'[Date]) = WEEKNUM(today())
        && 'Date'[date] <= TODAY ()
                     )

Many thanks for your input.

Best Regards,

Steven

[AlB · Accepted solution]

Hi @Anonymous

I guess the last argument in the logical and would need to be not TODAY but the equivalent of today last year, i.e.,

'Date'[date] <= DATE ( YEAR ( TODAY () ) - 1; MONTH ( TODAY () ); DAY ( TODAY () ) )

although I believe your code could be simplified in general by using time intelligence functions or just by defining the initial and end date for the week in the current year (which I guess you already have) and just subtracting one year 

[Anonymous]

Hi @AlB,

I changed the formula as per your advice and it's working well! 

Thank you very much for your help