cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
steve76 Regular Visitor
Regular Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

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

Hi @steve76

 

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 

2 REPLIES 2
Highlighted
Super User
Super User

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

Hi @steve76

 

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 

steve76 Regular Visitor
Regular Visitor

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

Hi @AlB,

 

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

Thank you very much for your help