Tag Archives: YTD

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

“Year to date” and “Last Year To Date” in MDX

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

To get “Year To Date” and “Last Year To Date” in MDX we can use the YTD function. To get “Last Year To Date” we also have to use the “ParallelPeriod” function. The “ParallelPeriod” gives us the same dates for an other period. In this case we switch back one year.

Here is a sample

WITH MEMBER [Year To Date] AS
( Sum (
Ytd ( [Date].[Calendar].CurrentMember ),
[Measures].[Internet Sales Amount]
) )
MEMBER [Last Year To Date] AS
( Sum (
Ytd (
ParallelPeriod ( [Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember )
),
[Measures].[Internet Sales Amount]
) )
MEMBER [Change] AS
divide ( [Measures].[Year To Date], [Last Year To Date] ),
FORMAT_STRING = 'Percent'
SELECT
{ [Measures].[Year To Date], [Measures].[Last Year To Date], [Measures].[Change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

And here is the result

YTD MDX

This code will work in Analysis Services Multidimensional. If you want to do the same for “Month to date”, “Quarter to date” or similar you can change YTD to MTD or QTD. If you plan to use week you should use an other hierarchy and then change to WTD.

I would recommend you to add this functionality as  calculations in the cube. There are several great scripts out there for you to copy. Such as DateTool by Marco Russo.

Get “Year To Date” and “Last Year To Date” in DAX

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

To get “Year To Date” and “Last year to date” in DAX is really simple. You can use the function TotalYTD and mix this with DATESBETWEEN or SAMEPERIODLASTYEAR.

This is an example with DATESBETWEEN

DEFINE
    MEASURE 'Internet Sales'[Previous Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                DATESBETWEEN (
                    'Date'[Date],
                    FIRSTDATE ( DATEADD ( 'Date'[Date], -12, MONTH ) ),
                    LASTDATE ( DATEADD ( 'Date'[Date], -12, MONTH ) )
                )
            )
        )
    MEASURE 'Internet Sales'[Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                'Date'[Date]
            )
        )
    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", 'Internet Sales'[Change]
    ),
    'Date'[Date] = DATE ( 2007, 01, 15 )
)

And this is an example with SAMEPERIODLASTYEAR

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'[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", 'Internet Sales'[Change]
    ),
    'Date'[Date] = DATE ( 2007, 01, 15 )
)

“Year To Date” in DAX and MDX

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

To get the sales for “year to date” you can use the TotalYTD function in DAX.

Below is a simple example on how this can be done.

EVALUATE
FILTER (
    SUMMARIZE (
        'Date',
        'Date'[Date],
        "Sales Year To Date", TOTALYTD (
            'Internet Sales'[Internet Total Sales],
            'Date'[Date]
        ),
        "Sales Quarter To Date", TOTALQTD (
            'Internet Sales'[Internet Total Sales],
            'Date'[Date]
        ),
        "Sales Month To Date", TOTALMTD (
            'Internet Sales'[Internet Total Sales],
            'Date'[Date]
        )
    ),
    'Date'[Date] = DATE ( 2006, 10, 01 )
)

The equal query in MDX might look like this

WITH MEMBER [Sales Year To Date]
AS 
	(SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]))

MEMBER [Sales Month To Date]
AS 
	(SUM(MTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]))

SELECT {
	[Measures].[Sales Year To Date],
	[Measures].[Sales Month To Date]
} ON 0,
[Date].[Calendar].[Year].&[2006].&[2].&[4].&[10 - October].&[10] ON 1
FROM [Adventure Works Internet Sales Model]

Please note that this query will give an error message if you run it against the AdventureWorks database in Tabular mode. This is because the year level is not defined as it should.

You will most likely get this error:

“Executing the query …
Query (3, 7) By default, a year level was expected. No such level was found in the cube.
Execution complete”

If you are running Analysis Services in multidimensional this query will work

WITH MEMBER [Sales Year To Date]
AS
(SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]))

MEMBER [Sales Month To Date]
AS
(SUM(MTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]))

SELECT {
[Measures].[Sales Year To Date],
[Measures].[Sales Month To Date]
} ON 0,
[Date].[Calendar].[Date].&[20110214] ON 1
FROM [Adventure Works]