Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sales amount between n days and current date

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!

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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