Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Formula to work out chargeable days

    Posted Apr 27, 2022 09:41 AM
    Edited by S M Apr 27, 2022 09:46 AM

    Hi there


    Hopefully someone smarter than me can help! I'm going to be using dummy data below, but it still tracks. 


    So, I have several timesheets from several different people. Let's say an entry looks like this: 


    Name Project Date/Time Start Date/Time End Type Hours
    Engineer 1 123 01/01/2022 8:00 01/01/2022 8:30 Travel 0.50
    Engineer 1 123 01/01/2022 8:30 01/01/2022 10:30 Engineering 2.00
    Engineer 1 123 01/01/2022 10:30 01/01/2022 11:00 Travel 0.50
    Engineer 1 N/A 01/01/2022 11:00 01/01/2022 11:30 Lunch 0.50
    Engineer 1 N/A 01/01/2022 11:30 01/01/2022 17:00 On Call 5.50


    With the current basic SWITCH I have set up, it'll put:


    2 Hrs to Engineering

    1 Hr to Travel

    30 Mins to Lunch 

    5 Hrs 30 to Not Utilised

    The SWITCH formulas looks like:

    Ut = SWITCH(TRUE(),
    'All Years'[jobcode_1] = "Bank Holiday", "i.",
    'All Years'[jobcode_1] = "Annual Leave", "i.",
    'All Years'[jobcode_1] = "Paternity Leave", "i.",
    'All Years'[jobcode_1] = "Sick - Unpaid", "i.",
    'All Years'[jobcode_1] = "Unpaid Leave", "i.",
    'All Years'[jobcode_1] = "Sick", "h.",
    'All Years'[jobcode_1] = "Compassionate Leave", "i.",
    'All Years'[jobcode_1] = "Break (Between Jobs)","f.",
    'All Years'[jobcode_1] = "Lunch Break", "f.",
    'All Years'[jobcode_1] = "2. Service Calls", "b.",
    'All Years'[jobcode_1] = "1. Tasks" && 'All Years'[jobcode_2] = "On Call - Service Support", "c.",
    'All Years'[jobcode_1] = "1. Tasks" && 'All Years'[jobcode_2] = "On Call - Systems Engineering", "d.",
    'All Years'[jobcode_1] = "1. Tasks" && 'All Years'[jobcode_2] = "Training", "e.",
    'All Years'[jobcode_1] = "1. Tasks" && 'All Years'[jobcode_2] = "Training Internal", "e.",
    'All Years'[jobcode_1] = "1. Tasks", "f.",
    'All Years'[jobcode_1] = "Furlough", "j.", 
    'All Years'[type] = "Unbillable", "g.", "a.")


    Key = SWITCH(TRUE(),
    'All Years'[Ut] = "a.", "Chargeable Work",
    'All Years'[Ut] = "b.", "Service",
    'All Years'[Ut] = "c.", "Reserved for Service and Not Used",
    'All Years'[Ut] = "d.", "Not Utilised",
    'All Years'[Ut] = "e.", "On Training",
    'All Years'[Ut] = "f.", "Unbillable Work - eg admin/internal",
    'All Years'[Ut] = "g.", "Overruns - Non-Billable",
    'All Years'[Ut] = "h.", "Sick",
    'All Years'[Ut] = "i.","Annual Leave",
    'All Years'[Ut] = "j.", "Furlough")

    I'd like a formula of some description that pretty much says: if on this date they had a job assigned, then they were utilised. If that makes sense? So I can get both views. 


    So like if Engineer 1 had a project assigned that day, count it as a chargeable day (so put 1) as I have a formula for the "is it a work day", and I'm then going to use that number to divide by the other to get a % amount, so I can't have 1 down multiple times for the same day & engineer, as it'll skew the figures. There's about 30 engineers and each day entry can have several entries, some of them unbillable and most of them billable. 


    Any help would be received gratefully 

  • 2.  RE: Formula to work out chargeable days

    Posted Apr 27, 2022 12:54 PM
    Hi Sarelle,

    Just quick question, Why you use SWITCH instead of build DIM table with CATEGORY of PayCode?
    Way you are doing it you use a lots of server resources.

    If you add another column, with information what category/PayCode is chargeable, what not.


    Adam Artur Boltryk
    Business Analyst

  • 3.  RE: Formula to work out chargeable days

    Posted Apr 28, 2022 04:48 AM
    Hi Adam

    Truth be told, I hadn't thought of creating a separate table primarily because I won't need to refresh it as it's not connected online, it's just Excel file(s) that I'll update each month with the new timesheet. But it may be a good idea to do regardless, so I'll look into that!

    You don't happen to have any idea of how to go about my original question by any chance do you? I feel like it may need to be a VAR measure, but I'm stuck on the logic.

    Sarelle Mather