Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Mattis
Frequent Visitor

Show project totals in rows

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.

 

Snip.JPG

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

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

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?

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!