Forum Discussion

Virtual_Ames's avatar
Virtual_Ames
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

Creative transform/DAX to generate a resource utilization viz given start date, end date, utilzation

Looking for best practices or a great solution to this problem:

 

Given a data feed that provides as pretty small table with the following data:

 

         Resource Name |  Project Name | Percent of Time Resource will dedicate to project | Start Date | End Date

 

        (Note: resources can have more than one project, projects will have multiple resources).

 

The goal is to be able to create a time series visualization showing the utilization level of a resource on any given day that is covered in our "earliest start date" to "latest end date" range.

 

Current approach is to merge this with a date table and have the following:

 

     Date | Project Name | Resource Name | Percent of Time Resource will dedicate to the project

 

This will generate a very long table as each date will have an entry for the N active resources assigned to all the active projects - so this could get bulky quickly. 10 projects with 5 folks for a year = 18250 rows....

 

First question is - what is the best set of transforms to generate a table like this?

 

Bonus - is there a way DAX can handle this at the formula level?                                                   

 

Thanks in advance....

 

Brian

 

 

 

 

Need Help

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    10 years ago

    Hi Brian,


    I'm jumping straight to the DAX solution as I think that's the best way of handling it.

     

    This is a variation on an 'events in progress' measure, see this paper (page 16 onwards):

    http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

     

    I've put a dummy example with the measure defined in a PBIX here:

    https://www.dropbox.com/s/s7fnsm0okicl8hs/Utilization.pbix?dl=0

     

    This measure is based on page 27 of the above paper.

    • Works if the Utilization and Date tables are not related.
    • Additive across all dimensions, so the measure basically gives you the number of Resource-Days (expressed as %).
      But can be redefined to give different behaviour.
      e.g. If someone is 50% on Project A and 25% on Project B, they will show as total Utilization = 75%.
      However, if someone is 100% utilized over two days and you show the total for two days, Utilization = 200%.
      Or if Resources X, Y and Z are each 25% utilized, total utilization = 75%

     

    Anyway, have a play with this to suit your needs :)

    =
SUMX (
    VALUES ( 'Date'[Date] ),
    SUMX (
        FILTER (
            GENERATE (
                SUMMARIZE (
                    Utilization,
                    Utilization[Start Date],
                    Utilization[End Date],
                    "UtilizationPercent", SUM ( Utilization[Percent of Time] )
                ),
                DATESBETWEEN ( 'Date'[Date], Utilization[Start Date], Utilization[End Date] )
            ),
            'Date'[Date] = EARLIER ( 'Date'[Date] )
        ),
        [UtilizationPercent]
    )
)

    Owen