Forum Discussion

skills29's avatar
skills29
Frequent Visitor
3 years ago
Solved

Measure that shows surplus projects

Hello everybody, 

I have two tables:

 

One shows the ProjectID and the total hours each project has:

 

The second table has the ProjectID, and how many worked hours by day each project also has:

 

I'm trying to create some measure which show me when the sum of hours worked in a specific project becomes higher than the total for that project. For example:

If Project 65FFAF05-38A2-44A5-814D-405DB5052877 has 2700 hours in total and in the day 28/06/2023 the sum of hours worked is 2700, so the next rows in the column WorkedHours has to be classified as a surplus project.


I haven't done any joins with both tables because there is a relationship between both tables. Is there any idea how can i do this? I'm stuck on this. Thanks

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    skills29 

    you can create a measure

    Column = if( sumx(FILTER('Table (2)','Table (2)'[projectid]=EARLIER('Table (2)'[projectid])&&'Table (2)'[date]<=EARLIER('Table (2)'[date])),'Table (2)'[workedhours])-RELATED('Table'[totalhours])>0,"surplus project")

    pls see the attachment below

1 Reply

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    skills29 

    you can create a measure

    Column = if( sumx(FILTER('Table (2)','Table (2)'[projectid]=EARLIER('Table (2)'[projectid])&&'Table (2)'[date]<=EARLIER('Table (2)'[date])),'Table (2)'[workedhours])-RELATED('Table'[totalhours])>0,"surplus project")

    pls see the attachment below