Calculate Sales Past 30 Days

[a68tbird]

Hello All -

  I can't quite figure out why my formula isn't working here. I'm trying to calculate the sales from the past 30 months using the following:

TotalSales30Day =
CALCULATE (
    SUM ( Orders[Quantity] ),
    DATESINPERIOD ( CalendarTable[Date], MAX ( CalendarTable[Date] ), -30, DAY )
)

but the result is blank. My CalendarTable is related to the sales table date.

The Dates columns are formatted as Date (not Date/Time). Quantity is a whole number.

Here's a snapshot of the Orders table (and yes, sales go back much further than today  😞

Thoughts?

Thanks

Travis

[johnmc]

Hi - i think you want...

TotalSales30Day =
CALCULATE (
    SUM ( Orders[Quantity] ),
    DATESINPERIOD ( CalendarTable[Date], LASTDATE ( CalendarTable[Date] ), -30, DAY )
)

[a68tbird]

Thanks johnmc, but that didn't seem to do anything for me either.

[v-cherch-msft · Accepted solution]

Hi @a68tbird

It seems you may try to use below measure:

TotalSales30Day =
CALCULATE (
    SUM ( Orders[Quantity] ),
    DATESINPERIOD ( Orders[OrderDate], MAX ( Orders[OrderDate] ), -30, DAY )
)

Regards,

Cherie

[a68tbird]

Thanks Cherie. That did work. I guess there's something wrong with my relationship between the calendar table and orders table? I can't understand why my original formula didn't work. All other examples of this formula that I've seen, use reference to the calandar table. 

But all good!