Forum Discussion

rsbin's avatar
rsbin
Community Champion
2 years ago
Solved

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 ...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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 🫡👍

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

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's avatar
rsbin
Community Champion
2 years ago

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,