Get stock for same day last year

[MarkSL]

Hi all,

I have a large fact table which contains StockDate, Product, Units and which is related to my Dates table on StockDate.

I have a matrix which has Products in the Rows and with the following 3 measures:

Stock (Today)

Stock (Yesterday)

Stock (Same Day Last Year)

And the following measures:

Tot Units= SUM(Units)
Stock (Today) = CALCULATE([Tot Units], Dates[Date] = TODAY())
Stock (Yesterday) = CALCULATE([Tot Units], Dates[Date] = TODAY()-1)

Instead of calculating Stock (Same Day Last Year) as Today()-365, I would prefer to use SAMEPERIODLASTYEAR or DATEADD -1 year, so it does cater for the correct date each year.

However as the field Date is not in my Matrix, there is no row context for date, therefore SAMEPERIODLASTYEAR does not work. 

I have tried creating a column on my date table called 'IsToday', which will return 'Yes' if the date is today.  However I am struggling to get my DAX correct to use this date to retrieve Stock (Same Day Last Year).

Tot Units SPLY = CALCULATE([Tot Units], dateadd(Dates[Date], -1, YEAR), Dates[IsToday] = "Yes")

Any thoughts appreciated!

Mark

[v-shex-msft · Accepted solution]

HI @MarkSL ,

You can try to use following measure formula to calculate same day last year sales:

Measure =
CALCULATE (
    [Tot Units],
    FILTER (
        ALLSELECTED ( Table ),
        [Date]
            = DATE ( YEAR ( TODAY () - 1 ), MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
)

Regards,

Xiaoxin Sheng

[MarkSL]

Hi @v-shex-msft ,

Many thanks that worked - I just had to make a small tweak as your code was returning yesterday, not same day last year, just a small typo I am sure:

Measure =
CALCULATE (
    [Tot Units],
    FILTER (
        ALLSELECTED ( Table ),
        [Date]
            = DATE ( YEAR ( TODAY () ) -1, MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
)