Forum Discussion

mohammadyousaf's avatar
mohammadyousaf
Resolver II
3 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
    mohammadyousaf
    3 years ago

    Thank you Anonymous 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    mohammadyousaf
    Resolver II
    3 years ago

    Thank you Anonymous 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]
        )
    )