Payroll Data

[rsbin]

Good Day Folks,

Having a bit of a brain cramp on this one....

I have the following Payroll Table Visual:

Week Start (Sun)	Date	Employee	Entity	Location	Hours Worked
1/21/2024	1/22/2024	101939	ABC	1	16
1/21/2024	1/21/2024	100037	XYZ	2	16

I then have different Payroll Calendars for each specific Entity.  For ABC, sample data as follows:

Entity	PayDate	StartDate	EndDate	TimesheetApproval
ABC	2/2/2024	1/21/2024	1/27/2024	1/29/2024
ABC	2/9/2024	1/28/2024	2/3/2024	2/5/2024

I would like a Measure to calculate "Days to Approval" ( 1/29/2024 - TODAY() ) to add to my first table visual, but must be specific by Entity.

Week Start (Sun)	Date	Number	Entity	Location	Hours Worked	Days to Approval
1/21/2024	1/22/2024	101939	ABC	1	16	5
1/21/2024	1/21/2024	100037	XYZ	2	16

As always, any help or guidance is much appreciated!
Kind Regards,

[Daniel29195 · Accepted solution]

@rsbin 

NB : im not sure if this code will work for you since i dont have an idea of the tables and the model you are using. 

the sample data i have worked on : 

no relation between the 2 tables 

final result : 

Measure 4 =

if(

    HASONEVALUE(data[Employee]),

    var selected_entity = VALUES(data[Entity])

    var _date = VALUES(data[Date])

    var datasource =

    SELECTCOLUMNS(

    FILTER(

        'calendar',

        'calendar'[Entity] in selected_entity && 'calendar'[StartDate] <= _date &&  'calendar'[TimesheetApproval]>= today()

    ),

    'calendar'[TimesheetApproval]

    )

    var calc = int(datasource - TODAY())

    RETURN

       if(

        ISBLANK(datasource) , blank(),

        calc

       )

)

NB :: you can change the filter conditions base on your business logic . .

let me know if it works for you . 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[rsbin]

@Daniel29195 ,

Hi Daniel, I didn't include a data model because it is quite complex.
It finally clicked to start down the Variable path.....so I'm working on a solution very similar to yours.

Appreciate your time and response on this.....confirmed for me I am on the right path to a more complete solution.

Best Regards,

[Idrissshatila]

@Daniel29195  check this