Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
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.

 

johnbasha33_0-1663863144858.png

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

Employee NumberApplied on From date To Date Expected Output 2WFH Date
1234520-08-202225-08-202226-08-2022 1234525-08-2022
3456725-08-202226-08-202231-08-2022 1234526-08-2022
8796922-08-202223-08-202206-09-2022 3456726-08-2022
     3456727-08-2022
Reporting week Aug 22nd to 26th    3456728-08-2022
Expected output 1    3456729-08-2022
     3456730-08-2022
Employee NumberNo of days of WFH applied for the week 3456731-08-2022
123452 Days   8796923-08-2022
345671 Day   8796924-08-2022
879694 Days   8796925-08-2022
     8796926-08-2022
     8796927-08-2022
     8796928-08-2022
     8796929-08-2022
     8796930-08-2022
     8796931-08-2022
     8796901-09-2022
     8796902-09-2022
     8796903-09-2022
     8796904-09-2022
     8796905-09-2022
1 ACCEPTED SOLUTION
v-xiaosun-msft
Community Support
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:

vxiaosunmsft_0-1664178416075.png

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.

vxiaosunmsft_1-1664178416081.png

vxiaosunmsft_2-1664178416083.png

Final output:

vxiaosunmsft_3-1664178416091.png

 

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.

View solution in original post

5 REPLIES 5
v-xiaosun-msft
Community Support
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:

vxiaosunmsft_0-1664178416075.png

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.

vxiaosunmsft_1-1664178416081.png

vxiaosunmsft_2-1664178416083.png

Final output:

vxiaosunmsft_3-1664178416091.png

 

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?

Greg_Deckler
Super User
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors