Forum Discussion

AUaero's avatar
AUaero
Icon for Responsive Resident rankResponsive Resident
6 years ago

DATESBETWEEN question

Hi,

I've got a data model with an orders table and a calendar table.  The order table contains the following columns:  Order Number, Transaction Date, and Request Date.  The calendar table contains:  Date Description, Working Day Flag.  A working day is indicated by a 1 in the column, a non-working day is represented by 0.
I want to be able to calculate the number of working days between the transaction date and the request date and display the result in a table consisting of Order Number, Transaction Date, Request Date and the measure # Days to Ship.  # Days to Ship is as follows; it eventually fails with an out of memory error:

 

# Days to Ship = 
CALCULATE(
    SUM('Calendar'[WORKING_DAY_FLAG]),
    DATESBETWEEN(
        'Calendar'[DATE_DESC],
        MAX(Orders[TRANSACTION_DATE]),
        MAX(Orders[REQUEST_DATE])
    )
)

 

 Any ideas?  Fundamentally what I want to do is pass the transaction date and request date from the order record to DATESBETWEEN and use the resulting date table as a filter argument for calculate.

2 Replies