Working Days.

[mohammadyousaf]

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

[v-rzhou-msft · Accepted solution]

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

[mohammadyousaf · Accepted solution]

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 · Accepted solution]

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