cancel
Showing results for
Did you mean:

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

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
Community Support

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

``````DimDate =
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.]

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

2 REPLIES 2
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]
)
)

Community Support

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

``````DimDate =
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.]

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors