Forum Discussion

JoeJames's avatar
JoeJames
New Member
1 year ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

JoeJames's avatar
JoeJames
New Member
1 year ago

Thanks it worked. How can I enhance this to calculate only working days (excluding weekends (Sat and Sun)?

 

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    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

    • JoeJames's avatar
      JoeJames
      New Member
      1 year ago
      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] ?

  • lovishsood1's avatar
    lovishsood1
    Resolver I
    1 year ago

    Try this CC:

    VAR StartDate = Sheet1[Transmittal Sent]
    VAR EndDate = Sheet1[WF Completed Date]
    RETURN
    CALCULATE (
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    CALENDAR ( EndDate ,StartDate ),
                    "WeekDay", WEEKDAY ( [Date], 2 )  //  Week starts from Monday
                ),
                [WeekDay] < 6 // This excludes Saturday (6) and Sunday (7)
            )
        )
    )