Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
JoeJames
New Member

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?

 

 

JoeJames_0-1727752723940.png

 

1 ACCEPTED SOLUTION
v-kongfanf-msft
Community Support
Community Support

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

vkongfanfmsft_0-1730100852762.png

 

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.

View solution in original post

8 REPLIES 8
v-kongfanf-msft
Community Support
Community Support

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

vkongfanfmsft_0-1730100852762.png

 

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
Memorable Member
Memorable Member

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
New Member

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

 

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

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

shafiz_p
Solution Sage
Solution Sage

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.