DAX to calculate total of projects with varying time periods

[Mattis]

Hi everybody,

I'm trying to calculate two allocation keys;

1. working hours as a percentage of total and
2. revenue as a percentage of the total

The keys are used to allocate overhead costs to different projects. The problem is that these projects have varying start and end dates, so if projects are listed in rows then the total should vary as well. You can see my relational set up below.

factFinActuals is my financials

factProjectActuals are the project table containg hours

dimWorks is a lookup table to factProjectActuals

dimDate is connected to factFinActuals via Posting Date

dimDate is connected to dimWorks via Ending date

I started out with a total that works fine but includes working hours not relevant to the project:

WorkingHoursTotal = 
   CALCULATE(
        SUM( factProjectActuals[Quantity] );
        ALLSELECTED( );
        factProjectActuals[Wage Type] = 3;
        factProjectActuals[Unit of Measure Code] = "TIMER"
   )

After browsing the forum I came up with this without any luck tough:

WorkingHours TRIAL = 
   CALCULATE(
        SUM( factProjectActuals[Quantity] );
        FILTER(
            dimWorks;
            dimWorks[Creation Date] <= CALCULATE( MAX( dimDate[Date] ) )
        );
        FILTER(
            dimWorks;
            dimWorks[Creation Date] >= CALCULATE( MIN( dimDate[Date] ) )
        );
        factProjectActuals[Wage Type] = 3;
        factProjectActuals[Unit of Measure Code] = "HOURS";
        ALLSELECTED( )
   )

Hope someone is able to figure this one out. Any help is highly appreciated.

Best,
Mathias

[Greg_Deckler]

Would need to see example/sample data for this to recreate and figure out what is going on. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, GENERATE may be the answer here, check out my quick measures here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

[Mattis]

Hi Greg,

Thank you for the reply. So the relevant tables are:

1. dimWorks (project lookup table)

2. factProjectActuals (project data)

3. factFinActuals (financial data)

4. dimDate (custom date table - min(01012014) - max(factProjectActuals[Posting Date]

1.

ProjectID, Starting Date, Ending Date

1, 01-01-2015, 03-02-2016

2, 01-02-2015, 04-04-2017

2.

ProjectID, Posting date, Wage Type, Unit of Measure Code, Quantity

1, 02-02-2016, 3, HOURS, 5

2, 01-03-2015, 3, HOURS, 10

3.

AccountNumber, Posting Date, Sum of Amount

 100100, 03-02-2015, 1.000

100100, 04-02-2015, 2.000

So basically in step 1, I need an allocation key for each project (hours/total hours). The problem is that the projects have varying starting and ending, so only hours held within these periods should appear in the total. In other words, each project should have different totals.

In step 2, these keys should be multipled on the factFinActuals table to allocate costs incurred within the starting and ending date of each project.

Let me know if you need any additional information. Meanwhile I'll check out the links you posted.

Thanks again.