Actual Working Hours

[Ashish9201]

Hi All,

I am trying to get prorate hours worked for new joiners  or those who leave the company mid month.

I have start(Emp Start Date) and end(Emp End Date) dates of employees in a column.

Have created below formula to get working Hours ;

Number of Working Hours =

VAR start_Date =

    FIRSTDATE ( 'Dim Date'[Date] )

VAR end_Date =

    LASTDATE ( 'Dim Date'[Date] )

VAR weekends =

    CALCULATE ( COUNT ( 'Dim Date'[Working Day] ), 'Dim Date'[Working Day] == 1 ) //DATEDIFF(start_Date,end_Date,Day)*8 +8 - weekends*8

RETURN

    CALCULATE (

        COUNT ( 'Dim Date'[Working Day] ),

        ALL ( msdyn_timeentry ),

        'Dim Date'[Working Day] == 0

    ) * 8

Below to get available hours ;

Available Hours =

SUMX (

    ADDCOLUMNS (

        SUMMARIZE ( 'msdyn_timeentry', 'msdyn_timeentry'[Team Member] ),

        "Aggregated Number of Working Hours2", 'msdyn_timeentry'[Number of Working Hours]

    ),

    [Number of Working Hours]

)

Can you please help me edit these formula to get prorate hours for joiners and leavers during mid month?

Thank you in advance!

Regards,

Ashish Gupta

[MFelix]

Hi  @Ashish9201 ,

Without any examples is difficult to make the change of the measure can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.