Calculate next business day based on conditions on columns from two different tables.

[sk007]

I have two tables Calendar table and TableA. 

Calendar Table 

TableA

There's a relation between these two tables using the Date key from Calendar table and end_date key from TableA. 

I would like to calculate a required_date field like this: 

I've already made the calendar table and calculated if_work_day (business day for a particular date). 

Date and if_work_day field comes from Calendar Table and rest of the fields comes from another table (TableA)

Logic is like this: 

If pay_frequency = bi_weekly && if pay_frequency = weekly: 

       required_date = end_date + 1 work day (only if_work_day in the next date is 1, if_work_day in the next date is 0, add another next date where if_work_day is 1)

if pay_frequency = semi_monthly && if pay_frequency = monthly:

      required_date = check_date - 3 work day (only if_work_day in that previous date is 1, if_work_day in the previous date is 0, go to another previous date where if_work_day is 1)

How can i do this? thank you!

[Anonymous]

Hi @sk007,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[tamerj1 · Accepted solution]

Hi @sk007 
Here is a sample file with the solution https://we.tl/t-ut86DgMNBs

Is Work Day? = 
CALCULATE ( 
    SELECTEDVALUE ( Table2[if_work_day] ), 
    CROSSFILTER ( Table1[ded_date], Table2[Date], BOTH ) 
)

required_date = 
VAR CurrentPayFrequency = SELECTEDVALUE ( Table1[pay_frequency] )
VAR CurrentDate = MAX ( Table1[ded_date] )
VAR WorkingDates = FILTER ( Table2, Table2[if_work_day] = 1 )
VAR DatesBefore = FILTER ( WorkingDates, Table2[Date] <= CurrentDate )
VAR DatesAfter = FILTER ( WorkingDates, Table2[Date] >= CurrentDate )
VAR DateBefore = MAXX ( DatesBefore, Table2[Date] )
VAR DateAfter = MINX ( DatesAfter, Table2[Date] )
RETURN
    SWITCH (
        TRUE ( ),
        CurrentPayFrequency IN { "MONTHLY", "SEMI_MONTHLY" }, DateBefore,
        CurrentPayFrequency IN { "WEEKLY", "BI_WEEKLY" }, DateAfter
    )