DAX measure Last Quarter Sales

1
votes

How to create DAX measure which calculates the sales of last quarter. I would like to see products with last quarter's sales. Say something like this:

+---------+-----------------------+
| Product | Sales of last quarter |
+---------+-----------------------+
| apple   |                    10 |
| banana  |                     5 |
| cherry  |                     8 |
+---------+-----------------------+

I feel that the solution might be a mixture of two filters:

  • DATESQTD
  • Range of dates

This is my best guess:

Sales_of_last_quarter =
VAR MaxDate =
    LASTNONBLANK(
        'Calendar'[Date],
        CALCULATE(
            [Sales]
        )
    )
VAR SalesByQuarter =
    CALCULATE(
        [Sales],
        DATESQTD( Calendar[Date] )
    )
VAR result =
    CALCULATE(
        SalesByQuarter,
        MaxDate
    )
RETURN
    result

Above measure works. However I wonder if it is possible to make it in one shot. This does not work:

Sales_of_last_quarter =
VAR MaxDate =
    LASTNONBLANK(
        'Calendar'[Date],
        CALCULATE(
            [Sales]
        )
    )

RETURN
SalesByQuarter =
    CALCULATE(
        [Sales],
        DATESQTD( Calendar[Date] ),
        MaxDate
    )

It does not work because MaxDate is single date (one day), but it should be a range of a quarter.

So the question is how to define the range of the last quarter.

1
powerbidax

1 Answers

1
votes

You may use CALCULATETABLE to get DATESQTD up to LASTNONBLANK date.

Sales of Last Quarter = 
CALCULATE (
    [Sales],
    CALCULATETABLE (
        DATESQTD ( 'Calendar'[Date] ),
        LASTNONBLANK ( 'Calendar'[Date], [Sales] )
    )
)