cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Dynamic Days calculation from 2 dates

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.

 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
1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Community Support

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
Not applicable

@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
Not applicable

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

Anonymous
Not applicable

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

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors