Get YTD two years back in DAX

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s