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.

Get YTD two years back in DAX

Posted on 8. November 2014 by Sindre • Posted in Advanced • Tagged DateAdd, datebetween, firstdate, lastdate, LYTD, totalytd, YTD • 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

Identify products with no sale in DAX

Posted on 16. October 2014 by Sindre • Posted in Basic script, DAX • Tagged Filter, isblank • Leave a comment

To identify products with no sale in DAX we can use the ISBLANK function.

Here is a sample

EVALUATE
FILTER (
    SUMMARIZE (
        'Product',
        'Product'[Product Name],
        "Sales", 'Internet Sales'[Internet Total Sales]
    ),
    ISBLANK ( 'Internet Sales'[Internet Total Sales] ) = TRUE
)

And if you want the products with sale you can switch “TRUE” with “FALSE”

EVALUATE
FILTER (
    SUMMARIZE (
        'Product',
        'Product'[Product Name],
        "Sales", 'Internet Sales'[Internet Total Sales]
    ),
    ISBLANK ( 'Internet Sales'[Internet Total Sales] ) = FALSE
)

Hierarchy testing in DAX (attempt number 2)

Posted on 11. October 2014 by Sindre • Posted in Advanced • Tagged DAX, hierarchy, isblank, measure, path, pathitem, pathitemreverse • Leave a comment

Here is some new query samples on how DAX hierarchies work. And this time it will work 🙂

Id did a little modification of the model. I had to make a new hierachy in the employee table.

Then you will have to deploy the model to the server.

My sample query looks like this:

DEFINE
    MEASURE 'Employee'[Employeelevel] =
        PATHLENGTH (
            PATH ( Employee[EmployeeKey], Employee[ParentEmployeeKey] )
        )
    MEASURE 'Employee'[ParentEmployee] =
        LOOKUPVALUE (
            'Employee'[Last Name],
            'Employee'[EmployeeKey], PATHITEMREVERSE (
                PATH (
                    'Employee'[EmployeeKey],
                    'Employee'[ParentEmployeeKey]
                ),
                2,
                1
            )
        )
    MEASURE 'Employee'[PathReverse] =
        PATHITEMREVERSE (
            PATH (
                'Employee'[EmployeeKey],
                'Employee'[ParentEmployeeKey]
            ),
            2,
            1
        )
    MEASURE 'Employee'[Path] =
        PATH (
            'Employee'[EmployeeKey],
            'Employee'[ParentEmployeeKey]
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Employee',
        'Employee'[EmployeeKey],
        'Employee'[Last Name],
        "Sales", 'Reseller Sales'[Reseller Total Sales],
        "Path", 'Employee'[Path],
        "PathItemReverse", 'Employee'[PathReverse],
        "Employeelevel", 'Employee'[Employeelevel],
        "ParentEmployee", 'Employee'[ParentEmployee]
    ),
    ISBLANK ( 'Reseller Sales'[Reseller Total Sales] ) = FALSE
)
ORDER BY 'Employee'[ParentEmployee], 'Employee'[EmployeeKey]

And the result looks like this

To verify the result I made an Excel pivot.

Testing some hierarchy stuff in DAX

Posted on 5. October 2014 by Sindre • Posted in Advanced, DAX • Tagged lookupvalue, path, pathitem • 3 Comments

Tonight I wanted to test some of the hierarchy functions in DAX. But after some testing I got very frustrated…

I have this sample DAX query

EVALUATE
FILTER (
    SUMMARIZE (
        ‘Product SubCategory’,
        ‘Product SubCategory'[Product SubCategory Name],
        “Sales”, ‘Internet Sales'[Internet Total Sales],
        “Path”, PATH (
            ‘Product SubCategory'[Product Subcategory Id],
            ‘Product SubCategory'[Product Category Id]
        ),
        “PathItem”, PATHITEM (
            PATH (
                ‘Product SubCategory'[Product Subcategory Id],
                ‘Product SubCategory'[Product Category Id]
            ),
            2,
            1
        ),
        “PathItemReverse”, PATHITEMREVERSE (
            PATH (
                ‘Product SubCategory'[Product Subcategory Id],
                ‘Product SubCategory'[Product Category Id]
            ),
            2,
            1
        ),
        “Product Category Name”, LOOKUPVALUE (
            ‘Product Category'[Product Category Name],
            ‘Product Category'[Product Category Id], PATHITEMREVERSE (
                PATH (
                    ‘Product SubCategory'[Product Subcategory Id],
                    ‘Product SubCategory'[Product Category Id]
                ),
                2,
                1
            )
        )
    ),
    ‘Internet Sales'[Internet Total Sales] <> 0
)
ORDER BY “Value PathItem” ASC

This produces this output

Except for the ordering this looks almost correct. But one row looks strange. The “Mountain Bikes” does not have any path at all.

If you pivot the same thing in Excel you will get this result.

Can anyone please help me with this problem?

How to use “CrossJoin” in DAX

Posted on 19. September 2014 by Sindre • Posted in Basic script • Tagged CrossJoin, DAX • 1 Comment

The “CrossJoin” function returns the Cartesian product of all rows from all tables in the arguments. In my sample below I want to have sales per year per city.

EVALUATE
ADDCOLUMNS (
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( Geography[City] )
),
'Date'[Calendar Year],
Geography[City]
),
"Sum of Sales", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
)

And the result looks like this

But as you can see I get a lot of empty rows. To remove these empty rows I add a filter to my query. I also moved my calculation outside the main query to make it more readable.

DEFINE
MEASURE 'Internet Sales'[Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
ADDCOLUMNS (
FILTER (
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( Geography[City] )
),
'Date'[Calendar Year],
Geography[City]
),
'Internet Sales'[Sales] <> 0
),
"Sum of Sales", 'Internet Sales'[Sales]
)

Basic query optimalization in DAX

Posted on 4. September 2014 by Sindre • Posted in Advanced, DAX • Tagged Addcolumns, CALCULATETABLE, clearcache, DAX, row • Leave a comment

What can we do to make queries as fast as possible? Here is some tips 🙂

In my simple test I want to see the sales for year 2009. To make sure my queries does not hit any cache I’m clearing it using XMLA. In my first query I will try to use the ROW function and sum up the internet sales

EVALUATE
ROW (
"Sales", CALCULATE (
SUM ( 'Internet Sales'[Sales Amount] ),
'Date'[Calendar Year] = 2008
)
)

When watching the profiler I can see that this query took 156 milliseconds. The reason why this takes quite long is that we are one the row level.

Lets switch to table level.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales", SUM ( 'Internet Sales'[Sales Amount] )
),
'Date'[Calendar Year] = 2008
)

This query took 78 milliseconds. Twice as fast as the first query.

What if I move the calculation using ADDCOLUMNS outside the SUMMARIZE?

EVALUATE
ADDCOLUMNS (
FILTER (
SUMMARIZE ( 'Date', 'Date'[Calendar Year] ),
'Date'[Calendar Year] = 2008
),
"Sales", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
)

This query took 62 milliseconds

Do you have an even better optimalization tip? Feel free to drop me an mail

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

A blog about Analytics, AWS and Azure

Category Archives: DAX