topic Re: Calculate new column based on multiple conditions from two different tables in DAX Commands and Tips

Calculate new column based on multiple conditions from two different tables

sk007 — Fri, 03 Jun 2022 10:50:48 GMT

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?

Re: Calculate new column based on multiple conditions from two different tables

Fowmy — Fri, 03 Jun 2022 10:31:58 GMT

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

Re: Calculate new column based on multiple conditions from two different tables

sk007 — Fri, 03 Jun 2022 10:39:02 GMT

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?

Re: Calculate new column based on multiple conditions from two different tables

Fowmy — Fri, 03 Jun 2022 10:51:31 GMT

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

Re: Calculate new column based on multiple conditions from two different tables

sk007 — Fri, 03 Jun 2022 10:51:47 GMT

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

Re: Calculate new column based on multiple conditions from two different tables

sk007 — Fri, 03 Jun 2022 10:52:20 GMT

Table2 is the date table itself

Re: Calculate new column based on multiple conditions from two different tables

tamerj1 — Fri, 03 Jun 2022 12:07:23 GMT

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 )