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.