Modeling data for a Project Portfolio ?

[tosrn]

Hello everyone ! 

Relatively new to PowerBI (IT consultant over here) and working for a client on Project Portfolio at the moment. 

• The goal: display for management a way to have the right data shown in order to properly pilot a Service Line.
• The numbers: talking about 60 different projects, 25 project managers, and a 2 years window
• The data: today there is an Excel model that is fed (line by line) with information such as Project ID, Name, Description, Workload, Start Date, End Date, Expected Benefits etc

• The question: how to properly model this data in Power BI ?

The main issue so far is with time: projects always have a start date & end date, and the main filter in the expected dashboard will be with a Time Slicer. Today I have a working "quick and dirty" way to filter this by having PowerBI create a flat table. For example a project from January to March would be:

"Project A - January - Workload X - Benefit Y"

"Project A - February - Workload X - Benefit Y"

"Project A - March - Workload X - Benefit Y"

Apart from the month, all other values are the same (yes, workload is an average).

This makes the slicer work fine, but it's (as expected) horrible for KPIs.

Example: "how much workload do I need to cover all the planned project from January to March?" Expected user behavior would be to look at the slicer, the dates, and then look at the big number "Required Workload". Problem is, with the flat list shown above, this will multiply the average workload 3 times: because it's 3 months project and therefore 3 lines.

Making a custom measure in PowerBI just for this makes me think that the model is not the right one.

Any thoughts? 

[AkhilAshok]

I think it will be easier if you handle this in measure. So datamodel remains as it originally is, with Project ID, Workload, Start Date, End Date, etc.

When you want to compute a measure, you could try (assuming you have a Date dimension):

Measure =
VAR FirstDay =
    MIN ( Date[Date] )
VAR LastDay =
    MAX ( Date[Date] )
RETURN
    CALCULATE (
        [Workload Measure],
        'Fact'[Start Date] <= FirstDay,
        'Fact'[End Date] >= LastDay
    )

In this approach, you don't need to setup a relationship between the Date dimension and the Fact table.