Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
)
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
)
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
Do you have a dates table connected to these two tables?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Table2 is the date table itself
User | Count |
---|---|
53 | |
35 | |
20 | |
16 | |
15 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |