How can I get the no of days or months an selected employee has worked in selected period.

[pmusram]

Hi Community!!

Kindly help me  with my query.

I have data consisting of Employee name, employee hire data, employee termination/release date.I want to calculate the no. of days or months any selected employee has worked in a selected year.

Example:- If I want to calculate no. of days or months an employee has worked in 2020 and maybe he has joined on 1 st Feb 2020 so I must get 11 months as the answer. If he has worked across 2020  then we must get 12 months. If employees has joined on 1st Feb 2020 and left 1st May 2020 we must get 3 months as the answer.

Basically we can say I need to slice the data of 2020 and if I select 2021 then days or months has to be calculated from today.

I am available for any query to explain here.

Thanks in advance

[amitchandak]

@pmusram , Check the Age page and current Age. if that can help in the attached file.

[pmusram]

@amitchandak Thanks for your solution. It has helped to understand my problem more clearly. But in your solution, I am getting the sum of months since employees' start date/hire date. But I need like How many months did employee work in between 2020 to 2021 or any selected period. Assume employee has joined in 1st May 2019 and still currently working with us. I need to calculate how many months did this employee worked only in 2018 to 2019 only like that.Hope you understood.Still Thanks a lot

[v-kkf-msft · Accepted solution]

Hi @pmusram ,

Try the following formula:

No. of day = 
var EndDate = 
    IF(
        ISBLANK( MAX('Table'[End Date]) ),
        TODAY(),
        MAX('Table'[End Date])
    )
VAR table_1 = 
    CALCULATETABLE(
        VALUES('Date'[Date]),
        FILTER(
            ALL('Date'),
            'Date'[Date] >= MAX('Table'[Start Date])
            && 'Date'[Date] <= EndDate
        )
    )
var table_2 = VALUES('Date'[Date])
return COUNTROWS( INTERSECT( table_1, table_2 ) )

No. of month = 
var EndDate = 
    IF(
        ISBLANK( MAX('Table'[End Date]) ),
        TODAY(),
        MAX('Table'[End Date])
    )
VAR table_1 = 
    CALCULATETABLE(
        VALUES('Date'[Year_Month]),
        FILTER(
            ALL('Date'),
            'Date'[Date] >= MAX('Table'[Start Date])
            && 'Date'[Date] <= EndDate
        )
    )
var table_2 = VALUES('Date'[Year_Month])
return COUNTROWS( DISTINCT(INTERSECT( table_1, table_2 )) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[pmusram]

@v-kkf-msft Thanks it's working great!!🤗