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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mohammadyousaf
Helper III
Helper III

Working Days.

Hi,  I am trying to count working days with the following data model. There are no direct relations between table-1 and table 2. 

I am trying to count number of days for common days as 1 day and for different dates 1 from each table where date doesn't match with each other. 

Any help is much appreciated. Thank you. 


Table-1 connected to Date Table
ID & Date 1 27-07-2023 2 28-07-2023 3 29-07-2023 4 30-07-2023 5 30-07-2023

Table-2 connected to Date Table
ID &  Date 1 27-07-2023 2 28-07-2023 3 29-07-2023 4 30-07-2023 5 31-07-2023

2 ACCEPTED SOLUTIONS
v-rzhou-msft
Community Support
Community Support

Hi @mohammadyousaf ,

 

I suggest you to add a weekday column in your date table.

DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2023, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Weekday", WEEKDAY ( [Date], 2 )
)

For reference:

Calculate Working Days in Power BI

Then create measures as below.

Count =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( DimDate, NOT ( DimDate[WeekDay] IN { 6, 7 } ) )
)

 

Best Regards,
Rico Zhou

 

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

mohammadyousaf
Helper III
Helper III

Thank you @v-rzhou-msft for the solution. However,  I have achieved it using 3 measures. 

This gives me the number of days for non blank values from both tables. 

1.  Tabl1_IDs = Calculate(
COUNTROWS (Tabl1), 
Filter(table1, Date))

2. Table2_IDs = Calculate(
COUNTROWS (Table2), 
Filter(Tabe2, Date)

No_Of_Days = 

COUNTROWS(
    FILTER(
        _calendar,
        [Table1_IDs] ||
        [Table2_IDS]
    )
)



View solution in original post

2 REPLIES 2
mohammadyousaf
Helper III
Helper III

Thank you @v-rzhou-msft for the solution. However,  I have achieved it using 3 measures. 

This gives me the number of days for non blank values from both tables. 

1.  Tabl1_IDs = Calculate(
COUNTROWS (Tabl1), 
Filter(table1, Date))

2. Table2_IDs = Calculate(
COUNTROWS (Table2), 
Filter(Tabe2, Date)

No_Of_Days = 

COUNTROWS(
    FILTER(
        _calendar,
        [Table1_IDs] ||
        [Table2_IDS]
    )
)



v-rzhou-msft
Community Support
Community Support

Hi @mohammadyousaf ,

 

I suggest you to add a weekday column in your date table.

DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2023, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Weekday", WEEKDAY ( [Date], 2 )
)

For reference:

Calculate Working Days in Power BI

Then create measures as below.

Count =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( DimDate, NOT ( DimDate[WeekDay] IN { 6, 7 } ) )
)

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.]

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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