Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
The table visualization below shows some of the fields that I have in my data table.
Transmittal sent is a column coming from a table called TRN History
WF Completed Date is a column in the DocReg Table
How can I create a measure that will calculate the number of days between Transmittal Sent and WF Completed Date?
Solved! Go to Solution.
Hi @JoeJames ,
You can try formula like below to create calculated column:
WorkdaysBetween =
VAR StartDate = 'Table'[StartDate]
VAR EndDate = 'Table'[EndDate]
VAR TotalDays = DATEDIFF(StartDate, EndDate, DAY)
VAR Weekends =
COUNTROWS(
FILTER(
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"DayOfWeek", WEEKDAY([Date], 2)
),
[DayOfWeek] = 6 || [DayOfWeek] = 7
)
)
RETURN
TotalDays - Weekends
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @JoeJames ,
You can try formula like below to create calculated column:
WorkdaysBetween =
VAR StartDate = 'Table'[StartDate]
VAR EndDate = 'Table'[EndDate]
VAR TotalDays = DATEDIFF(StartDate, EndDate, DAY)
VAR Weekends =
COUNTROWS(
FILTER(
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"DayOfWeek", WEEKDAY([Date], 2)
),
[DayOfWeek] = 6 || [DayOfWeek] = 7
)
)
RETURN
TotalDays - Weekends
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Calculate working days (excluding weekends):
Working_Days_Difference =
VAR StartDate = MIN('TRN History'[Transmittal Sent])
VAR EndDate = MIN('DocReg'[WF Completed Date])
RETURN
CALCULATE(
COUNTROWS(
CALENDAR(StartDate, EndDate)
),
NOT(WEEKDAY([Date], 2) IN {6, 7}) -- Exclude Saturdays (6) and Sundays (7)
)
Thanks it worked. How can I enhance this to calculate only working days (excluding weekends (Sat and Sun)?
Try this CC:
Ok, to acheive this, try below code:
DaysBetweenExcludingWeekends =
VAR StartDate = SELECTEDVALUE('DocReg'[WF Completed Date])
VAR EndDate = SELECTEDVALUE('TRN'[Transmittal Sent])
VAR TotalDays = DATEDIFF(StartDate, EndDate, DAY) + 1 -- +1 includes both the start and end date
VAR WeekendsCount =
COUNTROWS(
FILTER(
ADDCOLUMNS(
CALENDAR(StartDate, EndDate), -- Creating temporary calendar table for start and end date
"DayOfWeek", WEEKDAY([Date], 2) -- [Date] is the calendar table default column
),
[DayOfWeek] >= 6
)
) -- Counting only weekends between 2 dates(Saturday and Sunday)
RETURN
IF(HASONEVALUE('Table'[Received Date]),TotalDays - WeekendsCount) --Hasonevalue helps to remove calculation in the total area of table visual
Hope this helps!!
Don't forget to kudos!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
IF(HASONEVALUE('Table'[Received Date]),TotalDays - WeekendsCount) --Hasonevalue helps to remove calculation in the total area of table visual
Where did this column come from 'Table'[Received date] ?
Hi @JoeJames Try this:
DaysBetween =
DATEDIFF(SELECTEDVALUE('Table'[WF Completed Date]), SELECTEDVALUE('Table'[Transmittal Sent]), DAY)
Considering both the dates are present in table visuals as well as relationship exist between the tables.
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
Thanks @shafiz_p . How can I enhance this to calculate only working days (excluding weekends (Sat and Sun)?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
95 | |
92 | |
86 | |
69 |
User | Count |
---|---|
162 | |
129 | |
128 | |
104 | |
98 |