Dynamic Days calculation from 2 dates

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.