cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## 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.]

Resolver I

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
Resolver I

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.