Calculate new column based on multiple conditions from two different tables

[sk007]

I have two tables: Table1 (pay_frequency, ded_date) and Table2(Date, if_work_day).

Table1

Table2

Required Solution:

I want to calculate a new column (required_date) like this:

if Table1[pay_frequency]="BI_WEEKLY" and Table1[pay_frequency]="WEEKLY":

           if Table2[if_work_day]=0:

                 required_date = Table1[ded_date]+next date with if_work_day = 1

            else: 

                  required_date = Table1[ded_date]

if Table1[pay_frequency]="SEMI_MONTHLY" and Table1[pay_frequency]="MONTHLY":

           if Table2[if_work_day]=0:

                 required_date = Table1[ded_date] - previous date with if_work_day = 1

            else: 

                  required_date = Table1[ded_date]

How to do this in PowerBI?

[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
    )

[Fowmy]

@sk007 

Can you explain these two lines please? Also, confirm if you have any relationship between a Dates table and these two tables

required_date = Table1[ded_date]+next date with if_work_day = 1
required_date = Table1[ded_date] - previous date with if_work_day = 1

[sk007]

@Fowmy yes i do have relationship between Table2 and Table1 (one to many). if_work_day = 0 means its a holiday, if_work_day =1 means its the working day.

required_date = Table1[ded_date]+next date with if_work_day = 1

so when the if_work_day = 0, that means its a holiday. required_date shouldn't be on the holiday. for this one, it should be the next working day (if_work_day = 1) 1 means working day.

required_date = Table1[ded_date] - previous date with if_work_day = 1

for this condition,

so when the if_work_day = 0, that means its a holiday. required_date shouldn't be on the holiday (if_work_day=0). for this one, it should be the previous working day (if_work_day = 1) 1 means working day.

let me know if this helps?

[sk007]

@Fowmy i've also added a screenshot of required solution. 

[Fowmy]

@sk007 
Do you have a dates table connected to these two tables?

[sk007]

Table2 is the date table itself