Forum Discussion

wkeicher's avatar
wkeicher
Icon for Helper III rankHelper III
7 years ago
Solved

Calculating Event duration Hours by Resource and Date

I have a Table of events. Each Event has a date & duration( in hours). Each Event has a resource assigned through a relationship with an assignment table. . I am trying to build a visual of hours by ...
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    wkeicher issue is with resource table on many side. Either change the cross filter direction of relationship to both or write a measure use CROSSFILTER function in it.

  • Anonymous's avatar
    Anonymous
    7 years ago

    wkeicher - At issue here is that the Date table is not related to the fact table, so the calculation is counting all dates in the date table. As a best practice, you generally want to make relationships from your date dimension table to each fact table.

    Hope this helps,

    Nathan

wkeicher's avatar
wkeicher
Icon for Helper III rankHelper III
7 years ago

Anonymous  - So similiar to a different thread, I am now trying to provide a forecasted utilization% for upcoming events. With the help of the folks on this thread I am reporting the correct hours that are booked for future events. My next challenge is to calcuate utilization for each resource and the grouo they belong to.

 

I attempted to use the same column with modification, but getting much lower than expected results.

 

ForecastedUtil =
var workday_available_hours = COUNTROWS(FILTER('Working Dates','Working Dates'[IsWorkday] = "Yes")) * 7.5
var workday_booked_hours = CALCULATE(SUM('Events'[Hours]), 'Working Dates'[IsWorkday] = "Yes")
var non_workday_booked_hours = CALCULATE(SUM('Events'[Hours]), 'Working Dates'[IsWorkday] = "No")
return DIVIDE(
workday_booked_hours + non_workday_booked_hours,
workday_available_hours + non_workday_booked_hours
)
 
The sample below is to show one day (the 23rd)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

wkeicher - First step in troubleshooting is to create separate test measures for each variable to see where it is going wrong. Then we can analyze what is happening with that component.

Cheers,

Nathan

  • wkeicher's avatar
    wkeicher
    Icon for Helper III rankHelper III
    7 years ago

    Anonymous Very Helpful. So it looks like my caclulation for Available hours is incorrect. I tried both and single in the relationship no change.

     

  • wkeicher's avatar
    wkeicher
    Icon for Helper III rankHelper III
    7 years ago

    Anonymous Very Helpful. So it looks like my caclulation for Available hours is incorrect. I tried both and single in the relationship no change.

     

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      7 years ago

      wkeicher you should just look at what countrows return before calculation to make sure you are getting correct count. Also it depends what  is the filter context for the working date table? Are you filtering the data or what else is in matrix?

       

      Just looking at dax expression doesn't help to resolve the issue, you have to provide the full context on how you are using it.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        wkeicher - At issue here is that the Date table is not related to the fact table, so the calculation is counting all dates in the date table. As a best practice, you generally want to make relationships from your date dimension table to each fact table.

        Hope this helps,

        Nathan