Forum Discussion

cprine's avatar
cprine
Frequent Visitor
5 years ago
Solved

Using DATESBETWEEN with relative date ranges

Hello,    I am attempting to sum balances of invoices in a relative date range in the past. However, it returns some results outside of the date window. As of today (10/18/20) it should ONLY return...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You should have a Calendar Table with a relationship to the invoice date column.  To your visual, drag Date from the Calendar Table.  Write this measure

    30-59 OVERDUE Balances = CALCULATE(SUM('ACCOUNTS_PAYABLE'[INVOICE_AMOUNT]),ACCOUNTS_PAYABLE[Unpaid?] in {"Unpaid"},DATESBETWEEN(Calendar[DATE], TODAY()-59, TODAY()-30) 
)

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

You should have a Calendar Table with a relationship to the invoice date column.  To your visual, drag Date from the Calendar Table.  Write this measure

30-59 OVERDUE Balances = CALCULATE(SUM('ACCOUNTS_PAYABLE'[INVOICE_AMOUNT]),ACCOUNTS_PAYABLE[Unpaid?] in {"Unpaid"},DATESBETWEEN(Calendar[DATE], TODAY()-59, TODAY()-30) 
)

Hope this helps.

  • cprine's avatar
    cprine
    Frequent Visitor
    5 years ago

    Thank you Ashish_Mathur ! That fixed the issue. 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      You are welcome.