DAX - Calculate sum of the last 365 days

Question

I want to calculate the sum of sales of the last 365 days.

The following function works very well if there is a day filter in the visual (chart, table, etc.). But - and this is where the problem lies - if there's not a day filter, but a month for example, then the measure returns the sales of the last 12 months.

So, today (9.11.2017), I should get the sum of the sales from 9.11.2016 until 8.11.2017 - regardless of what filter is applied. And not the sum of the sales from 1.12.2016 until 30.11.2017.

SalesTTM =
IF (
    TODAY () >= FIRSTDATE ( 'calendar'[date] );
    CALCULATE (
        'order'[Sales];
        DATESBETWEEN (
            'calendar'[date];
            NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'calendar'[date] ) ) );
            LASTDATE ( 'calendar'[date] )
        )
    )
)

In the DATESBETWEEN function above I can't find a way to express "tomorrow less one year" until "today". How can I do that?

Alexis Olson Alexis Olson · Accepted Answer · 2017-11-09T16:09:12

You need to put something in there to ignore the filtering on the month. Try inserting ALL('calendar'[date]); before your DATESBETWEEN ( line.

SalesTTM =
VAR LastDate = LASTDATE ( 'calendar'[date] )
RETURN IF (
    TODAY () >= FIRSTDATE ( 'calendar'[date] );
    CALCULATE (
        'order'[Sales];
        ALL('calendar'[date]);
        DATESBETWEEN (
            'calendar'[date];
            NEXTDAY ( SAMEPERIODLASTYEAR ( LastDate ) );
            LastDate
        )
    )
)

DAX - Calculate sum of the last 365 days

1 Answers