Tag Archives: DateAdd

Get YTD two years back in DAX

Posted on by • Posted in Advanced • Tagged , , , , , , • Leave a comment

In this sample I will show you how you can get YTD (year to date), LYTD (last year to date) and even YTD for two years back or more. When getting YTD we will use the TotalYTD function. And for LYTD we will use TotalYTD in combination with SAMEPERIODLASTYEAR. But if you want YTD for 2 years back you have to add even additional functions.

Lets see the query

DEFINE
    MEASURE 'Internet Sales'[Previous Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                SAMEPERIODLASTYEAR ( 'Date'[Date] )
            )
        )
    MEASURE 'Internet Sales'[Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                'Date'[Date]
            )
        )
    MEASURE 'Internet Sales'[Year To Date 2 year] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                DATESBETWEEN (
                    'Date'[Date],
                    DATEADD ( FIRSTDATE ( 'Date'[Date] ), -2, YEAR ),
                    DATEADD ( LASTDATE ( 'Date'[Date] ), -2, YEAR )
                )
            )
        )
    MEASURE 'Internet Sales'[Change] =
        FORMAT (
            'Internet Sales'[Year To Date]
                / 'Internet Sales'[Previous Year To Date],
            "Percent"
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Date',
        'Date'[Date],
        "Previous Year To Date", 'Internet Sales'[Previous Year To Date],
        "Sales Year To Date", 'Internet Sales'[Year To Date],
        "Change YTD vs LYTD", 'Internet Sales'[Change],
        "Year to date 2 years ago", 'Internet Sales'[Year To Date 2 year]
    ),
    'Date'[Date] = DATE ( 2008, 01, 15 )
)

And the output will look like this

YTD 2 years back

Comparable day in DAX

Posted on by • Posted in Date functions • Tagged , , • Leave a comment

In many companies they want to compare todays sales with the comparable day last year. To find the comparable day we subtract 364 from todays date. In this sample todays date is 15 january 2007.

To see what dates we should get we can run this SQL

select '2007/01/15' as TodaysDate, DateName(dw, '2007/01/15') As 'DayName', 
dateadd(day, -364, '2007/01/15') As ComparableDay, DateName(dw, dateadd(day, -364, '2007/01/15')) As ComparableDayName

And this will return this result

Comparable day 3

To check the values for these dates we can run these DAX queries

EVALUATE
FILTER (
    ADDCOLUMNS (
	VALUES ('Date'[Date]),
        "Sales this day", 'Internet Sales'[Internet Total Sales]
    ),
    'Date'[Date] = DATE ( 2007, 01, 15 )
)

EVALUATE
FILTER (
    ADDCOLUMNS (
	VALUES ('Date'[Date]),
        "Sales this day", 'Internet Sales'[Internet Total Sales]
    ),
    'Date'[Date] = DATE ( 2006, 01, 16 )
)

The final query where we put all together looks like this

EVALUATE
FILTER (
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "Comparable day", CALCULATE (
            'Internet Sales'[Internet Total Sales],
            DATEADD ( 'Date'[Date] , -364, Day)
        ),
        "Sales this day", 'Internet Sales'[Internet Total Sales]
    ),
    'Date'[Date] = DATE ( 2007, 01, 15 )
)

Comparable day 4

Please note that this sample requires a date dimension that has the date 2006/01/16. If you are unsure about this you will have to change the query. The -364 approach will have issues if last year was a leap years.