Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Need help with working out Cumulative Target Hours across timeframe

Hi there,

 

I am currently working on a report which looks at timesheets of engineers. I have calculated the Cumulative CAPEX and Total hours by using the following DAX:

 

Cumulative Capex Hours =
CALCULATE (
[Total Capex Hours],
FILTER (
ALLSELECTED ( 'Calendar'[Date].[Date] ),
'Calendar'[Date].[Date] <= MAX ( 'Calendar'[Date].[Date] )
)
)
 
Cumulative Working Hours =
CALCULATE (
[Jira Logged Hours],
FILTER (
ALLSELECTED ( 'Calendar'[Date].[Date] ),
'Calendar'[Date].[Date] <= MAX ( 'Calendar'[Date].[Date] )
)
)
 
I am now trying to work out the Cumulative Target hours by using:
Cumulative Target Hours =
VAR NoEmp =
DISTINCTCOUNT( TeamsandRoles[Developer Name] )
VAR Target =
CALCULATE (
[Target Hours Per Day],
FILTER (
ALLSELECTED ( 'Calendar'[Date].[Date] ),
'Calendar'[Date].[Date] <= MAX ( 'Calendar'[Date].[Date] )
)
)
RETURN
NoEmp * Target
 
But it doesn't seem to work - I keep getting the following (all of the engineers hours are the same):

What is the best way to ensure that there are distinct Cumulative Target hours for the engineers?

 

I look forward to hearing from you guys!

 

Cheers 🙂

Need Help

3 Replies

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

    Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Try like

     

    Cumulative Target Hours =
    VAR NoEmp =
    DISTINCTCOUNT( TeamsandRoles[Developer Name] )
    VAR Target =
    CALCULATE (
    [Target Hours Per Day],
    FILTER (
    ALLSELECTED ( 'Calendar'[Date].[Date] ),
    'Calendar'[Date].[Date] <= MAX ( 'Calendar'[Date].[Date] )
    ), filter(allselected(TeamsandRoles[Developer Name] ), TeamsandRoles[Developer Name] =max(TeamsandRoles[Developer Name] ))
    )
    RETURN
    NoEmp * Target

     

     

     

    or

     

     

    Cumulative Target Hours =
    VAR NoEmp =
    DISTINCTCOUNT( TeamsandRoles[Developer Name] )
    VAR Target =
    CALCULATE (
    [Target Hours Per Day],
    FILTER (
    ALLSELECTED ( 'Calendar'[Date].[Date] ),
    'Calendar'[Date].[Date] <= MAX ( 'Calendar'[Date].[Date] )
    ), filter(allselected(TeamsandRoles[Developer Name] ), TeamsandRoles[Developer Name] =max(TeamsandRoles[Developer Name] ))
    )
    RETURN
     Target

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amit,

      I have screenshotted the following and blocked out sensitive data (i.e. names):

      I think an issue might be the measure for target hours, which is the following:

      Target Hours Per Day = COUNTROWS( 'Calendar') * 4.44
       
      I look forward to hearing from you!
       
      Cheers 🙂
  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

    Has your question solved? If solved ,you could share your solution then mark it .If not ,could you pls share your pbix file ? Remember to remove confident data.

     

    Best Regards

    Luicien