Get number of days since last sales in DAX

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

Count days 1

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.

Advertisements

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