Help in creating a calculated column

[JoeJames]

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?

[v-kongfanf-msft · Accepted 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.

[Kedar_Pande]

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)
)

[JoeJames]

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

[lovishsood1]

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)

        )

    )

)

[shafiz_p]

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]

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] ?

[shafiz_p]

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]

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