DAX - Calculate sum of the last 365 days

[webportal]

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?

[v-jiascu-msft · Accepted solution]

Hi @webportal,

Try this formula please. The file you can try: https://1drv.ms/u/s!ArTqPk2pu-BkgUbwxIYm90ewakJg

SalesTTM 2 =
IF (
    TODAY () >= FIRSTDATE ( 'calendar'[date] ),
    CALCULATE (
        SUM ( sales[quantity] ),
        DATESINPERIOD ( 'calendar'[Date], TODAY (), -1, YEAR )
    )
)

As you can see from the picture, I debugged the formula with [startday] and [endday].

Best Regards!

Dale

[webportal]

Yes, that works.
Thanks!