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 DocReg Table

 

How can I create a measure that will calculate the number of days between Transmittal Sent and WF Completed Date?

 

 

 

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.

8 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    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

    • JoeJames's avatar
      JoeJames
      New Member
      1 year ago

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

  • 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)
              )
          )
      )
  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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)
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    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.