Forum Discussion
Payroll Data
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,
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())RETURNif(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 🫡👍
3 Replies
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())RETURNif(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 🫡👍
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,
Daniel29195 check this
