Sales amount between n days and current date

[Anonymous]

Alright everyone, I believe I'm close on this one because it works, but only on the days I refresh the data (currently a once per month report). The DAX I used is here (60 and 90 day are calculated with the same statement, only the day number changed): 

Sales Last 30 =

CALCULATE(SUM(Table_1046[Extended Price]),

DATESINPERIOD(Table_1046[Invoice Date], TODAY(), -30, DAY)

)

Here is the screenshot of it functioning

 

Here is a screenshot of it any day other than the updated day

Greatly appreciate any help to finish this up. Thanks!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

I would suggest you generate a date dimension table ('DateDim') with CALENDAR function. Establish a one to many relationship from 'DateDim' to Table_1046 based on date field.

Modify the measure formula as:

Sales Last 30 =
CALCULATE(SUM(Table_1046[Extended Price]),
DATESINPERIOD(DateDim[Date], TODAY(), -30, DAY)
)

Regards,

Yuliana Gu

[Anonymous]

This worked and I got to learn something new. Thanks for your help!!