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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.