Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Cumulative hours per phase

Hi everyone,

I'm staring at this problem for days now, and can't find a good solution. I think i'm close but not quite.

What I want to plot is the cumulative sum of booked hours on a project based on the phase it was in the moment the hours were booked.

For this I have the following data model;

Projects have an actual phase, but the snapshot table contains what it was back in time, together with the date.

Hours are booked on projects in the booking table together with a date.

These two dates is where the difficulty lies for me.

What measure do I need to show hours booked cumulatively per phase?

The top graph is showing the cumulative hours correctly.

Adding the 'phase' field in the legend makes it not correct. The total hours is now 5 times as high, and all phases have the same hours...

What I think is needed is some sort of if statements that makes sure only hours are summed if the snapshot date = booking date.

Not sure if the measure is readable in the screenshot.

Hours Cumulative How I think it should be = CALCULATE(SUM('Booking'[Hours]),FILTER(ALLSELECTED('Booking'), 'Booking'[Date] <= MAX('Booking'[Date])),
FILTER(ALLSELECTED(Snapshots),Snapshots[Date] = 'Booking'[Date])
)

For Booking[date] there are obviously multiple records, and an error is thrown.

Please help me out.
Many thanks.
Hugo

 

p.s. How do I upload the demo file??

 

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Look at your data model. There is no way Snapshots and Bookings can influence each other. (Follow the filter arrows)

     

    That means you are just creating a cartesian product - as you noticed, five times the same value.

     

    Change your data model or use lookupvalue/treatas.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks lbendlin 

       

      This is the downside of making a small model to figure it out in. In the screenshotted data model the direction isn't set to 'both' where in the actual model they are. Thanks for pointing this out!

       

      I do feel I came a step closer!

       

      Below measure gives almost the desired result.

      Hours Cumulative = 
CALCULATE(SUM('Booking'[Hours]),
  FILTER(KEEPFILTERS('Booking'),
  'Booking'[Date] <= MAX('Booking'[Date]) && 
  'Booking'[Date] <= MAX(Snapshots[Date])
  )
)

       

      In red I have corrected how I would like the results to be.

       

      As is visible in the March snapshot the phase has changed. Yet all hours are now counted towards the new phase (2), instead of the hours booked in phase 1 remain constant and the extra 5 are listed under phase 2.

       

       

      Any help is appreciated.

      Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    It's a little hard to follow up...please provide me with some data sample or share me with your pbix file from your Onedrive for Business after removing sensitive data.

     

    Best Regards,
    Eyelyn Qin