Calculate WTD sales for same week last year, but for the same number of elapsed days

[Anonymous]

Hi!

Im struggeling with a time int. measure to calculate total sales last year. The main goal is to be able to show a table with [Department], [#Sales WTD] and [#Sales WTD_LY)] - without the need of any slicers at all, where the measure it self takes into account the current weeknumber based on NOW() / TODAY(). 

This is working fine with some easy measures, but my main problem is that [#Sales WTD_LY] calculates the sum for the whole week last year, and not for instance: only sales from monday until thursday (todays day). This makes it quite useless a KPI.

So - does anyone know how to take this into account?

Thanks! (Mock data set = https://ufile.io/uarzc)

(My week starts on Mondays FYI )

#SalesTotal = SUM([NetAmount])

#SalesTotal_LY = CALCULATE([#SalesTotal];DATEADD(Dates[Date];-1;YEAR))

#SalesWTD = 
CALCULATE([#SalesTotal];
    FILTER(
        Dates;
            YEAR( Dates[Date] ) = YEAR( NOW() )
                && WEEKNUM( Dates[Date];2 ) = WEEKNUM( NOW();2)
    )
)

#SalesWTD_LY = 
CALCULATE([#SalesTotal_LY];
    FILTER(
        Dates;
            YEAR( Dates[Date] ) = YEAR( NOW() )
                && WEEKNUM( (Dates[Date]);2 ) = WEEKNUM( NOW();2)
    )
)

[Anonymous · Accepted solution]

@Anonymous,

Create the following columns in your Dates table.

WeekNumber = WEEKNUM(Dates[Date],2)

Year = YEAR(Dates[Date])

Then create the following measure in your sales table.

SameWeekLastYear = IF (
    HASONEVALUE ( 'Dates'[Year] )
        && HASONEVALUE ('Dates'[WeekNumber] ), CALCULATE(
        sales[#SalesTotal],
        FILTER (
            ALL ( 'Dates' ),
            'Dates'[Year] = VALUES ( 'Dates'[Year] )-1
                && 'Dates'[WeekNumber] = VALUES ( 'Dates'[WeekNumber] )
                && 'Dates'[WeekNumber] = WEEKNUM(NOW(),2 )
        )
    ))

Reference:
http://www.dutchdatadude.com/ultimate-time-based-calculations-cheat-sheet-for-dax-power-bi-including-week-based-calculations/

Regards,
Lydia