Forum Discussion

cprine's avatar
cprine
Frequent Visitor
6 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
    6 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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

cprine , Seem fine.

 

Try like

30-59 OVERDUE Balances =
var _min = TODAY()-59
var _max - TODAY()-30
return
CALCULATE (
SUM('ACCOUNTS_PAYABLE'[INVOICE_AMOUNT]),
filter(ACCOUNTS_PAYABLE, ACCOUNTS_PAYABLE[Unpaid?] in {"Unpaid"} && ACCOUNTS_PAYABLE[INVOICE_DUE_DATE] >= _min && ACCOUNTS_PAYABLE[INVOICE_DUE_DATE] <=_max)
)

 

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    5 years ago

    super

    so that a 0 comes out in case there is no data I have completed it with

    var _min = today()-365
    var _max = today()
    VAR Abiertos365 =
    CALCULATE(...
    ...
    )
    RETURN
    IF(ISBLANK(Abiertos365),0, Abiertos365)
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      Hi,

      You may try this measure

      =coalesce([Abiertos365],0)

      Hope this helps.