Useful filters for your time dimension (DAX / Tabular)

Posted on 22. June 2015 by Sindre • Posted in Date functions • Tagged lastnonblank • Leave a comment

It is always nice to have some time filters in your Tabular model. Because of reuse I try to create most of these filters in the data source. But if that is not an option you can off course add them directly to your Tabular model. So here are some useful samples.

The first one will give you a flag on the last date with sales.

First lets create a new measure.

Last Sales Date:=LASTNONBLANK('Date'[Date]; CALCULATE(SUM('Internet Sales'[Sales Amount])))

And then add a new column in the date dimension. I my sample I will call this column “Last Date With Sales”

=IF('Date'[Date] = calculate('Date'[Last Sales Date];ALL('Date'));1;0)

So if you need the last date with sales in your report you can add the dimension attribute “Last Date With Sales” and then filter the value to 1. This is quite handy if you are using date functions such as TotalYTD or TotalMTD. Please note that you can use the FIRSTNONBLANK functions to. This will return the first date with sales.

The next flag will be a flag that indicates tomorrows date.

To make this flag I will create a new column and add the following expression

=IF('Date'[Date] = TODAY()-1;1;0)

To test this in AdventureWork you first have to figure out how many days to subtract. This can be done by using the following SQL

select datediff(day, '2010/01/02', getdate())

And then change your expression to something like this

=IF('Date'[Date] = TODAY()-1983;1;0)

To get the current month you can add a column like this

=IF(Month('Date'[Date]) = MONTH(TODAY()-1) &amp;&amp; YEAR('Date'[Date]) = YEAR(TODAY()-1);1;0)

And to get the current year you can add a column like this

=IF(YEAR('Date'[Date]) = YEAR(TODAY()-1);1;0)

Get number of days since last sales in DAX

Posted on 3. January 2015 by Sindre • Posted in Date functions • Tagged DAX, MAX, MIN • Leave a comment

Today I want to show you an sample on how to count the number of days since last sales. And the number of days between first sale and last sale.

The code is quite easy to understand

DEFINE
    MEASURE 'Internet Sales'[First order date] =
        CALCULATE ( MIN ( 'Internet Sales'[Order Date] ) )
    MEASURE 'Internet Sales'[Last order date] =
        CALCULATE ( MAX ( 'Internet Sales'[Order Date] ) )
EVALUATE
 (
    FILTER (
        SUMMARIZE (
            'Geography',
            'Geography'[City],
            "First order", 'Internet Sales'[First order date],
            "Last order", 'Internet Sales'[Last order date],
            "Days between", 1 * 'Internet Sales'[Last order date]
                - 'Internet Sales'[First order date],
            "Days since last order", 1 * TODAY ()
                - 'Internet Sales'[Last order date],
            "Sales", FORMAT (
                'Internet Sales'[Internet Total Sales],
                "Currency"
            )
        ),
        'Internet Sales'[Internet Total Sales] <> 0
    )
)
ORDER BY 'Internet Sales'[Last order date] DESC

And the result will look like this

A geek will always think; “how can I make this query run faster?”

One thing is to replace summarize with addcolumns. As in the sample below.

DEFINE
    MEASURE 'Internet Sales'[First order date] =
        CALCULATE ( MIN ( 'Internet Sales'[Order Date] ) )
    MEASURE 'Internet Sales'[Last order date] =
        CALCULATE ( MAX ( 'Internet Sales'[Order Date] ) )
EVALUATE
 (
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'Geography'[City] ),
            "First order", 'Internet Sales'[First order date],
            "Last order", 'Internet Sales'[Last order date],
            "Days between", 1 * 'Internet Sales'[Last order date]
                - 'Internet Sales'[First order date],
            "Days since last order", 1 * TODAY ()
                - 'Internet Sales'[Last order date],
            "Sales", FORMAT (
                'Internet Sales'[Internet Total Sales],
                "Currency"
            )
        ),
        'Internet Sales'[Internet Total Sales] <> 0
    )
)
ORDER BY 'Internet Sales'[Last order date] DESC

If you check the execution time in SQL Server Profiler you will see that query 2 runs faster than query 1.

Comparable day in DAX

Posted on 14. August 2014 by Sindre • Posted in Date functions • Tagged Comparable days, DateAdd, DAX • 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

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 )
)

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.

Running total in DAX

Posted on 10. August 2014 by Sindre • Posted in Date functions • Tagged DatesYtd, DAX • Leave a comment

To get a running total in DAX you can use the DATESYTD function.

Here is an example that gives running total per day for a given range.

EVALUATE
FILTER (
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "Running total", CALCULATE (
            'Internet Sales'[Internet Total Sales],
            DATESYTD ( 'Date'[Date] )
        ),
        "Sales per day", 'Internet Sales'[Internet Total Sales]
    ),
    'Date'[Date] < DATE ( 2006, 01, 20 )
        && 'Date'[Date] >= DATE ( 2006, 01, 01 )
)

And here is an example on how to get running total per month.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Month Name],
'Date'[Calendar Year],
"Running total", CALCULATE (
'Internet Sales'[Internet Total Sales],
DATESYTD ( 'Date'[Date] )
),
"Sales per month", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2006
)

DYNAMIC DATE FILTER IN DAX USING VBA

Posted on 4. August 2014 by Sindre • Posted in Date functions • Tagged DAX, DYNAMIC DATE, FORMAT, INT, NOW, VBA • Leave a comment

If you are creating scheduled reports in DAX it might be handy to add some VBA to calculate time periods automatically.

Here is an example that returns yesterday

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Date],
"Sales", 'Internet Sales'[Internet Total Sales]
),
	FORMAT('Date'[Date], "yyyy-MM-dd") = FORMAT(NOW() - 1, "yyyy-MM-dd")
)

If you want to test a valid date in the sample database you can figure out how many days you have to remove by running this SQL “select DATEDIFF(day, getdate(), ‘2006/05/18’)”. And then subtract this number instead of “NOW() – 1”.

And if you want to have a similar query for year this can look like this

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales", 'Internet Sales'[Internet Total Sales]
),
	'Date'[Calendar Year] = INT(FORMAT(NOW() - 1, "yyyy"))
)

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

Posted on 14. July 2014 by Sindre • Posted in Date functions • Tagged Datesbetween, DAX, FORMAT, LYTD, SAMEPERIODLASTYEAR, YTD • 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 13. July 2014 by Sindre • Posted in Date functions • Tagged MDX, Month to date, MTD, Year To date, YTD • 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]

A blog about Analytics, AWS and Azure

Category Archives: Date functions