Need calculation filtering data from another table with many to many relationship

[Anonymous]

Hello All,

It is my first post so please accept appologise if some information may be inconsistent. 

I have started working on report that should provide real data with some technology utilisation statistics including amount of hours, time spend, number of working hours etc. I was able to implement some of requirements by creating Calendar table (Work Days Calendar) and Temporary table (Tech Utilisation) to show key stats but I stumbled on problem with adjustment of working hours based on holiday taken. 

To work out how many days each engineer worked each month I need to substract amount of holiday taken but I cannot achieve this because of type of many to many relationships between

• Tech Utilisation [FullName] and Leave Planner Enginer  [FullName]
• Tech Utilisation [Start Date] and Leave Planner [StartDatetime]

So far I got manage to calculate total number of holiday taken using below formula  but I cannot filter data on date ranges. All td does it produces cartesian product so the data is wrong.

Tech Utilisation = VAR TEMPTABLE =

DISTINCT (

SELECTCOLUMNS ('Billing Totals', "CreatedDate", 'Billing Totals'[CreatedDate], "iWeekDay", 'Billing Totals'[iWeekDay])

)

RETURN

ADDCOLUMNS (

CROSSJOIN (TEMPTABLE, VALUES('Billing Totals'[Engineer])),

"Billable Hours Sum", IF (

ISBLANK ( CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) ) ),

0,

CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) )

),

"Non Billable Sum", IF (

ISBLANK ( CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) ) ) ,

8,

IF(CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) >= 8 ), 0, ABS(CALCULATE ( SUM ( 'Billing Totals'[Billable Hours] ) ) - 8))

),

"Time Spent",

IF(ISBLANK(CALCULATE(SUM('Billing Totals'[Hours Spent]))), 0, CALCULATE((SUM('Billing Totals'[TotalTimeSpent]))))/ 60,

"Time Dur",

CALCULATE(SUM('Leave Planner'[LeaveDuration_Quantity])

, FILTER('Leave Planner','Leave Planner'[FullName] = EARLIER('Billing Totals'[Engineer]))),

)

And  below is a screenshoot of the report with tech stats.

I was trying for few days to find out how diffrently I could make it working but always finished with same result either I could use date slicer or from Leave Planner to be able to filter on date ranges. 

I would appreciate if someone could propose any hint or help me out to find solution.

Best,

[v-joesh-msft]

Hi @Anonymous ,

You can only filter the many side of the table relationship on the one side of the table relationship, so you need to use the "Date" in the Calendar table (Work Days Calendar) for filtering. I would like to know what is the formula for "holiday taken". Could you please share your sample data and expected result if you don't have any Confidential Information? Please upload your files to One Drive and share the link here.

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.