Need help with working out Cumulative Target Hours across timeframe

[Anonymous]

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 🙂

[v-luwang-msft]

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

[amitchandak]

@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]

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 🙂