Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
sk007
Regular Visitor

Calculate new column based on multiple conditions from two different tables

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

Table1

sk007_0-1654251250880.png

Table2

sk007_1-1654251265252.png

Required Solution:

sk007_0-1654253427786.png

 

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?

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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
    )

 

1.png

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

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
    )

 

1.png

Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

sk007
Regular Visitor

@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
Regular Visitor

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

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

sk007
Regular Visitor

Table2 is the date table itself

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.