Dynamic Day calculation based on weekly

[Anonymous]

Hi Guys, i have a complex scenario as explained below.

i want to pull a weekly report for employees who applied for no of days of WFH in a week.

i want to find this based on WFH applied dates.

can you please tell me what is the best approach to capture the no of days for the selected week?

from the below example is Out put1 or Out put2 is possibile? how to achieve both the outputs?

But we need to calculate the no of days only for the given week, an employee can apply wfh for multiple days but we need to count the days only for the given week based on the from and to date.

@tamerj1 @amitchandak @Jihwan_Kim @daXtreme @Greg_Deckler @Pragati11 @mwegener 

@parry2k @lbendlin @PaulDBrown 

Employee Number	Applied on	From date	To Date		Expected Output 2	WFH Date
12345	20-08-2022	25-08-2022	26-08-2022		12345	25-08-2022
34567	25-08-2022	26-08-2022	31-08-2022		12345	26-08-2022
87969	22-08-2022	23-08-2022	06-09-2022		34567	26-08-2022
					34567	27-08-2022
Reporting week Aug 22nd to 26th					34567	28-08-2022
Expected output 1					34567	29-08-2022
					34567	30-08-2022
Employee Number	No of days of WFH applied for the week		34567	31-08-2022
12345	2 Days				87969	23-08-2022
34567	1 Day				87969	24-08-2022
87969	4 Days				87969	25-08-2022
					87969	26-08-2022
					87969	27-08-2022
					87969	28-08-2022
					87969	29-08-2022
					87969	30-08-2022
					87969	31-08-2022
					87969	01-09-2022
					87969	02-09-2022
					87969	03-09-2022
					87969	04-09-2022
					87969	05-09-2022

[PaulDBrown · Accepted solution]

Here is one way, which basically involves expanding the from / to dates.

Create a new table using:

Expanded =
VAR _EXP =
    GENERATE (
        'Original Table',
        CALENDAR ( 'Original Table'[From date ], 'Original Table'[To Date] )
    )
RETURN
    SELECTCOLUMNS (
        _EXP,
        "_Employee Num", 'Original Table'[Employee Number],
        "_App on", 'Original Table'[Applied on ],
        "_Dates", [Date]
    )

Create a Calendar Table and a dimension table for employees using:

Calendar Table =
ADDCOLUMNS (
    CALENDAR ( MIN ( Expanded[_Dates] ), MAX ( Expanded[_Dates] ) ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] ),
    "WeekNum", WEEKNUM ( [Date] ),
    "W #", "W" & WEEKNUM ( [Date] )
)

Employee Table = 
DISTINCT('Original Table'[Employee Number])

 Create single direction one-to-many relationships between these new tables and the corresponding fields in the "Expanded" table. The model looks like this:

Next create the following measure:

WFH Dates = 
COUNT(Expanded[_Dates])

Create a table visual with 'Employee Table'[Employee] and the measure to get:

 Create a new table visual with the 'Employee Table'[Employee] and 'Calendar Table'[Date] (rename the latter to whatever you wish) to get

 Create a matrix visual with 'Employee Table'[Employee] as rows, 'Calendar Table'[W#) as columns and the [WFH Dates] measure to get:

Attached is the sample PBIX file

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to attached sample file with the solution

Mumber of Days = 
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Dates1 =
    CALENDAR ( MinDate, MaxDate )
RETURN
    SUMX (
        'Table',
        VAR Dates2 =
            CALENDAR ( 'Table'[From Date], 'Table'[To Date] )
        RETURN
            COUNTROWS ( FILTER ( INTERSECT ( Dates1, Dates2 ), NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } ) ) )
    )

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to attached sample file with the solution

Mumber of Days = 
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Dates1 =
    CALENDAR ( MinDate, MaxDate )
RETURN
    SUMX (
        'Table',
        VAR Dates2 =
            CALENDAR ( 'Table'[From Date], 'Table'[To Date] )
        RETURN
            COUNTROWS ( FILTER ( INTERSECT ( Dates1, Dates2 ), NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } ) ) )
    )

[Anonymous]

@tamerj1 thank for your brilliant walkthrough, allow me with some time to review this analysis and will confirm the same.

[PaulDBrown · Accepted solution]

Here is one way, which basically involves expanding the from / to dates.

Create a new table using:

Expanded =
VAR _EXP =
    GENERATE (
        'Original Table',
        CALENDAR ( 'Original Table'[From date ], 'Original Table'[To Date] )
    )
RETURN
    SELECTCOLUMNS (
        _EXP,
        "_Employee Num", 'Original Table'[Employee Number],
        "_App on", 'Original Table'[Applied on ],
        "_Dates", [Date]
    )

Create a Calendar Table and a dimension table for employees using:

Calendar Table =
ADDCOLUMNS (
    CALENDAR ( MIN ( Expanded[_Dates] ), MAX ( Expanded[_Dates] ) ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] ),
    "WeekNum", WEEKNUM ( [Date] ),
    "W #", "W" & WEEKNUM ( [Date] )
)

Employee Table = 
DISTINCT('Original Table'[Employee Number])

 Create single direction one-to-many relationships between these new tables and the corresponding fields in the "Expanded" table. The model looks like this:

Next create the following measure:

WFH Dates = 
COUNT(Expanded[_Dates])

Create a table visual with 'Employee Table'[Employee] and the measure to get:

 Create a new table visual with the 'Employee Table'[Employee] and 'Calendar Table'[Date] (rename the latter to whatever you wish) to get

 Create a matrix visual with 'Employee Table'[Employee] as rows, 'Calendar Table'[W#) as columns and the [WFH Dates] measure to get:

Attached is the sample PBIX file

[Anonymous]

@PaulDBrown , one more check i want to do, how to know any given day is holiday or not? how can we exclude Holidays from these dates?

[PaulDBrown · Accepted solution]

To manage holidays, you will need to import a table listing public holidays. In this example I imported public holidays for the UK from the Uk-gov website.

 You can then use a measure to subtract WFU days which occur on a holiday as follows:

Non Hols WFH = 
VAR _NWD = CALCULATE(COUNT(Expanded[_Dates]), FILTER('Calendar Table', 'Calendar Table'[Date] IN VALUES('Holiday Table'[Date])))
RETURN
COUNT(Expanded[_Dates]) - _NWD

 Or you can include the holidays in a new column in the calendar table

 And use this measure to exclude holidays:

WFH non hol Days (Calendar) =
CALCULATE (
    COUNT ( Expanded[_Dates] ),
    FILTER ( 'Calendar Table', ISBLANK ( 'Calendar Table'[Holiday] ) )
)

New file attached

[Anonymous]

@PaulDBrown you are truley awesome. thank you.

[Anonymous]

@tamerj1 @PaulDBrown  i have to say you guys are brilliant, allow me with some time to review these provided solutions and will confirm the same.