Dynamic Days calculation from 2 dates

[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 

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

[v-xiaosun-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I created a sample, and here is my solution.

To get the expected output 1, DATEDIFF function does help.

Create a column:

No of days of WFH applied for the week =
IF (
    COUNTROWS (
        FILTER (
            'Table 1',
            'Table 1'[Employee Number] = EARLIER ( 'Table 1'[Employee Number] )
                && 'Table 1'[To Date] <= DATE ( 2022, 8, 26 )
                && 'Table 1'[From date ] >= DATE ( 2022, 8, 22 )
        )
    ) > 0,
    DATEDIFF ( 'Table 1'[From date ], 'Table 1'[To Date], DAY ) + 1,
    IF (
        COUNTROWS (
            FILTER (
                'Table 1',
                'Table 1'[Employee Number] = EARLIER ( 'Table 1'[Employee Number] )
                    && 'Table 1'[To Date] > DATE ( 2022, 8, 26 )
                    && 'Table 1'[From date ] >= DATE ( 2022, 8, 22 )
            )
        ) > 0,
        DATEDIFF ( 'Table 1'[From date ], DATE ( 2022, 8, 26 ), DAY ) + 1
    )
)

Final Output:

To get the expected output 2, please use Power Query to create a custom column. And then expand to new rows, you will get the expected result.

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Greg_Deckler  i am trying to get the count of days for the selected week only how can a measure do that? a measure will give total no of days between 2 dates. but how do we compare with a week and get the no of days?

[Anonymous]

@amitchandak @Pragati11 you have any solutions for my question?

[Anonymous]

@tamerj1 @amitchandak @Jihwan_Kim @daXtreme @Greg_Deckler @Pragati11 @mwegener  this is a complicated scenario, need your support please is it possible or not?

[Greg_Deckler]

@Anonymous going to need to create a CALENDAR table in a VAR between the 2 dates. Use ADDCOLUMNS to add a WEEKNUM. Strikes me as similar to these: 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365