Forum Discussion

Teachjanderson's avatar
Teachjanderson
New Member
3 years ago
Solved

Transactions between dates with weekends

I'm bringing in transactions which are disbursed on different days of the month depending on the customers program (1st, 15th, 21st, 28th, last day of the month, etc). For example, any transactions falling between the 15th of this month and next month would be assigned to the next disbursement date unless either of those dates fall on the weekend, then they move to the next business day. (4/15/2023 becomes 4/17/2023 so customers with disbursements on 15th are then counted if between 4/17-5/15 and so on). I'd like to create an expected disbursement date for each transaction. 

Looking for the best way to model and create this in BI. Appreciate tips or examples of creating this for each of the customers and their various disbursement dates. 

Need Help
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    3 years ago

    Hi Teachjanderson ,

     

    You can calculate for the day of week of day name a disbursement date is and move it to the next weekday. That would be +1 for Sun and +2 for Saturday.

    =
VAR __DAY_OF_WEEK =
    WEEKDAY ( DisbursementDates[Disbursement date], 1 )
RETURN
    SWITCH (
        __DAY_OF_WEEK,
        1, DisbursementDates[Disbursement date] + 1,
        7, DisbursementDates[Disbursement date] + 2,
        DisbursementDates[Disbursement date]
    )

    As for the expected disbursement date of each transaction and following that if the  date is 4/15 the disbursement date would be 4/17, you can calculate for the earliest expected disbursement date that is greater than the  current transaction date.

    =
CALCULATE (
    MIN ( DisbursementDates[Expected Disbursement Date] ),
    FILTER (
        DisbursementDates,
        DisbursementDates[Expected Disbursement Date]
            > EARLIER ( TransactionDates[Transaction date] )
    )
)

     

     

1 Reply

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi Teachjanderson ,

     

    You can calculate for the day of week of day name a disbursement date is and move it to the next weekday. That would be +1 for Sun and +2 for Saturday.

    =
VAR __DAY_OF_WEEK =
    WEEKDAY ( DisbursementDates[Disbursement date], 1 )
RETURN
    SWITCH (
        __DAY_OF_WEEK,
        1, DisbursementDates[Disbursement date] + 1,
        7, DisbursementDates[Disbursement date] + 2,
        DisbursementDates[Disbursement date]
    )

    As for the expected disbursement date of each transaction and following that if the  date is 4/15 the disbursement date would be 4/17, you can calculate for the earliest expected disbursement date that is greater than the  current transaction date.

    =
CALCULATE (
    MIN ( DisbursementDates[Expected Disbursement Date] ),
    FILTER (
        DisbursementDates,
        DisbursementDates[Expected Disbursement Date]
            > EARLIER ( TransactionDates[Transaction date] )
    )
)