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 dates between 8/20/20 through 9/19/20. Most items are in that range... however, a handful of results on the table are outside of that range... and I'm about to pull my hair out lol.

Any help would be awesome!

 

 

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

 

 

 

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.

6 Replies

  • 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.

  • amitchandak's avatar
    amitchandak
    Super User
    5 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
      Administrator
      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
        Super User
        5 years ago

        Hi,

        You may try this measure

        =coalesce([Abiertos365],0)

        Hope this helps.