Forum Discussion
Solved
Help in creating a calculated column
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 Do...
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 - WeekendsBest Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks it worked. How can I enhance this to calculate only working days (excluding weekends (Sat and Sun)?
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 visualWhere did this column come from 'Table'[Received date] ?