DAX Sales Total for the Same DAY (not Date) Last Year -- How does this work?

[victoryamaykin]

Could someone please explain how to write a DAX measure for the total sales of the same DAY last year? 
I've seen the other posts, but none of them worked for me. 

I am looking for the sales on the same day of the week, not the date. 

---- 
(not working DAX) 

Sales Same Day Last Year =

    CALCULATE (

        [Total Sales],  -- Sum sales for the calculated dates

        FILTER (

            'Calendar',

            'Calendar'[Same Day Last Year] = SELECTEDVALUE(Data[Date]) -- What were the sales on the same day of the week last year

        )

    )

---- 

Here is my file
https://drive.google.com/file/d/1Ppu2BR1ZZ_vpxM3jCKnmHgUzGuTI0XTJ/view?usp=sharing

[audreygerred]

Hi! You can make the folowing measures:

Total Sales = SUM ('YouTable'[YourSalesField]

Total Sales LY = CALCULATE ([Total Sales], SAMEPERIODLASTYEAR ('DateTable'[Date])

Then, in your table, put in the date field from your date table and both of your measures. Have a date filter and filter to today (or whatever day you want) and the Total Sales measure will display this year amount and Total Sales LY will be the same date in the previous year.

I updated your file 🙂 https://drive.google.com/file/d/1Ppu2BR1ZZ_vpxM3jCKnmHgUzGuTI0XTJ/view?usp=drive_link

[victoryamaykin]

Thank you, but I am looking for the same day, not the same date last year. Any help is appreciated greatly. I'm still new to DAX, so I don't fully understand the FILTER function. 

I have a calculated column that finds the same day last year. 

Same Day Last Year =

VAR _dt = [Date]

VAR _weekDay = WEEKDAY(_dt, 2)  -- Returns the day of the week (Monday=1, Sunday=7)

VAR _yearAgo = _dt - 364        -- Approximate the date a year ago

VAR _weekAdjustment =

    _weekDay - WEEKDAY(_yearAgo, 2) -- Difference in weekday from the same date last year

RETURN

    _yearAgo + _weekAdjustment

--- 

But this DAX code is not working: 

--- 

Sales Same Day Last Year =

    CALCULATE (

        [Total Sales],  -- Sum sales for the calculated dates

        FILTER (

            'Calendar',

            'Calendar'[Same Day Last Year] = SELECTEDVALUE(Data[Date]) -- What were the sales on the same day of the week last year

        )

    )

[audreygerred · Accepted solution]

Ah - here ya go:

SalesSameWeekDayLastYear =

VAR CurrentDate = MAX('Calendar'[Date])

VAR CurrentWeek = WEEKNUM(CurrentDate)

VAR CurrentDayOfWeek = WEEKDAY(CurrentDate)

VAR LastYearDate =

    CALCULATE(

        MAX('Calendar'[Date]),

        'Calendar'[Year] = YEAR(CurrentDate) - 1,

        WEEKNUM('Calendar'[Date]) = CurrentWeek,

        WEEKDAY('Calendar'[Date]) = CurrentDayOfWeek

    )

RETURN

    CALCULATE(

        [Sales],

        'Calendar'[Date] = LastYearDate

    )

[victoryamaykin]

It's working in the test file, but I can't get it to work in my main file. Any idea why it's the same number? 

Items Produced Same Week Day Last Year =

VAR CurrentDate = MAX(DimDates[Date])

VAR CurrentWeek = WEEKNUM(CurrentDate)

VAR CurrentDayOfWeek = WEEKDAY(CurrentDate)

VAR LastYearDate =

    CALCULATE(

        CurrentDate,

        DimDates[Year] = YEAR(CurrentDate) - 1,

        WEEKNUM(DimDates[Date]) = CurrentWeek,

        WEEKDAY(DimDates[Date]) = CurrentDayOfWeek

    )

RETURN

    CALCULATE(

        [Items Produced],

        DimDates[Date] = LastYearDate

    )

[audreygerred]

Hi! Looks like you have this:

Items Produced Same Week Day Last Year =

VAR CurrentDate = MAX(DimDates[Date])

VAR CurrentWeek = WEEKNUM(CurrentDate)

VAR CurrentDayOfWeek = WEEKDAY(CurrentDate)

VAR LastYearDate =

    CALCULATE(

        CurrentDate,

        DimDates[Year] = YEAR(CurrentDate) - 1,

        WEEKNUM(DimDates[Date]) = CurrentWeek,

        WEEKDAY(DimDates[Date]) = CurrentDayOfWeek

    )

RETURN

    CALCULATE(

        [Items Produced],

        DimDates[Date] = LastYearDate

    )

But, you need to have this:

SalesSameWeekDayLastYear =

VAR CurrentDate = MAX('Calendar'[Date])

VAR CurrentWeek = WEEKNUM(CurrentDate)

VAR CurrentDayOfWeek = WEEKDAY(CurrentDate)

VAR LastYearDate =

    CALCULATE(

        MAX('Calendar'[Date]),

        'Calendar'[Year] = YEAR(CurrentDate) - 1,

        WEEKNUM('Calendar'[Date]) = CurrentWeek,

        WEEKDAY('Calendar'[Date]) = CurrentDayOfWeek

    )

RETURN

    CALCULATE(

        [Sales],

        'Calendar'[Date] = LastYearDate

    )

They produce different results and the one I provided works 🙂

[victoryamaykin]

Got it, thanks so much!