Dax Function to Calculate same day last year

[Nanakwame]

Hi All,

I have a function calculating sales yesterday which is 

Sales Yesterday = CALCULATE(SUM(Sales[salesamount]),'Date'[Date] = TODAY() -1). 

I am looking to also calculate the sales for the same day last year. I tried using sameperiod() function but i am getting the same exact value as sales yesterday which i know is not right based on the data check. Is there any other function i can use to achieve the same result?

Thank you 

[Ashish_Mathur]

Hi,

In your Table visual, assuming you have dragged Year/Month and date from Date Table, this measure should work

=CALCULATE(SUM(Sales[salesamount]),sameperiodpastyear('Date'[Date]))

[AlexisOlson · Accepted solution]

There are a variety of ways to do this but the most straightforward might be to compute the date explicitly.

Sales Yesterday LY =
VAR _Y = TODAY () - 1
VAR _YLY = DATE ( YEAR ( _Y ) - 1, MONTH ( _Y ), DAY ( _Y ) )
RETURN
    CALCULATE ( SUM ( Sales[salesamount] ), 'Date'[Date] = _YLY )

Note: I'm not sure if this would work if yesterday were a leap day.

[MarkLaf]

I was curious about the question on leap day, so did a quick test. Using time intelligence functions will keep result in February, whereas DATE will convert to March 1. See below.

Also, @Nanakwame, check out DAX Guide for how to use SAMEPERIODLASTYEAR. They explain how to use in CALCULATETABLE to return mutliple dates. You can see how I use it in CALCULATE below to return a single date/value. Also included DATEADD version for those interested in a method than can be modified for conversions other than last year.

(note, the output is a table so needs to be pasted into a New Table formula for those interested in testing in PBI desktop)

LastYearTest = 
// For the below to work, set up another table first 
// with CALENDAR( DATE( 2024, 1, 1 ), DATE( 2024, 12, 31 ) )
// and mark it as a date table
VAR BaseCal = 
    GENERATE(
        CALENDAR( DATE( 2024, 1, 1 ), DATE( 2024, 12, 31 ) ), //2024 is a leap year
        VAR _dt = [Date]

        //Designated date table's _dt equivalent for time intelligence funcs
        VAR _dt_T = CALCULATETABLE( Dates, TREATAS( { _dt }, Dates[Date] ) )

        VAR _MinusYear =  DATE ( YEAR ( _dt ) - 1, MONTH ( _dt ), DAY ( _dt ) )
        VAR _SamePeriodLastYear = CALCULATE( SAMEPERIODLASTYEAR( Dates[Date] ), _dt_T )
        VAR _DateAdd = CALCULATE( DATEADD( Dates[Date], -1, YEAR ), _dt_T )
        
        RETURN
        ROW(
            "MinusYear",_MinusYear,
            "SamePeriodLastYear",_SamePeriodLastYear,
            "DateAdd", _DateAdd
        )
    )
VAR CheckLeapDate =  FILTER( BaseCal, [Date] >= DATE(2024, 2, 28) && [Date] <= DATE(2024, 3, 1) )
RETURN
CheckLeapDate

Output:

[Nanakwame]

Thanks @AlexisOlson 

That worked.