Show project totals in rows

[Mattis]

Hi there,

I want project numbers in my rows and then sum up the number of hours performed. This is succesfully accomplished with the following formula (the two filters are just standard criteria in my data):

WorkingHours = 
   CALCULATE(
        SUM( factProject[Quantity] );
        factProject[Wage Type] = 3;
        factProject[Unit of Measure Code] = "HOURS"
   )

Next, I want a column which displays the total across all projects. The problem is that these projects have varying starting and ending dates, which will have to be taken into account. I've attempted this with the following code:

WorkingHoursTotal = 
   CALCULATE(
        [WorkingHours];
            FILTER( factProject; factProject[Posting Date] >= MIN( factProject[Creation Date] ) &&
                                        factProject[Posting Date] <= MAX( factProject[Ending Date] ) );
        ALL( factProject[WorksNo] )
   )

The column [WorkNo] is the project ID. For some reason, this formula is returning the same result as the first piece of code. I might have the logic upside down regarding the MIN MAX functions. Anyway, all help is appreciated. Let me know if additional information is needed.

[v-yuta-msft]

Hi Mattis,

Based on your description, you want to calculate the total value between the start date and end date in your slicer, right?

To achieve this, you can try DAX formula like below:

WorkingHoursTotal =
CALCULATE (
 [WorkingHours],
 ALLSELECTED ( ProjectActuals[Posting Date] ),
 ALL ( ProjectActuals[WorksNo] )
)

Regards,

Jimmy Tao

[Mattis]

Hi @v-yuta-msft

I want to calculate the total between the starting date and ending date of my project. So if any other projects intersects this period, then they have to be added up as well.

For example, one project A stretches from 1/1 - 31/1 with a 100 hours. Project B streches from 15/1 - 15/2 with 100 hours. 50 of the hours was performed in january, so the total for january has to return 150 hours. This will allow me to say that project A was responsible for (100/150) hours in january.

The relevant variables are Starting Date, Ending Date, Posting Date (related to hours) and quantity (hours). The starting and ending dates are both in a fact table and in a dim table, cause I tried to see if that made a difference.

So when I place all projects in a column, as seen in the screen shot from my original post, and add the total next to it, I'd like the code to figure out the starting date and ending date of every project and return the total.

Any ideas?