Split Duration Across months Using Power Query

I wan to split amount total time across months using Power Query. I have a report in product which does similar thing but users are complaining it is too slow and demand that I use Power Query. I implemented the same using Power Query - Expanded column which does comes out faster. The issue now is that data is sometimes off from what is in production which as been verified to be correct.
For example, in production, I have:

And that has been tested to be correct.

With the current one I am working on, I have:

The logic here is to put into consideration the start date in a particular month for individual Job_ID and the lenght of days it took, spreading it across the available month.

The DAX code in production is like so:

Split of Hours =

----- Calculation of Total Duration Hours -----

VAR _Total_Duration_Hours =
SUM( ‘Project Data’[Total Duration(Hours)] )

----- Calculation of Consolidated Total Hire Days -----

VAR _Total_Hire_Days =
CALCULATE(
VAR _Table_1 =
FILTER( DATES ,
DATES[Date] >= SELECTEDVALUE( ‘Project Data’[Project Start Date] ) &&
DATES[Date] <= SELECTEDVALUE( ‘Project Data’[Project End Date] ) )

VAR _Hire_Days = 
COUNTROWS( _Table_1 )

RETURN
_Hire_Days ,
    ALL( Dates ) )

----- Calculation of Individual Hire Days -----

VAR _Table_2 =
FILTER( DATES ,
DATES[Date] >= SELECTEDVALUE( ‘Project Data’[Project Start Date] ) &&
DATES[Date] <= SELECTEDVALUE( ‘Project Data’[Project End Date] ) )

VAR _Individual_Hire_Days =
COUNTROWS( _Table_2 )

RETURN
DIVIDE( _Total_Duration_Hours * _Individual_Hire_Days , _Total_Hire_Days , 0 )

Now, I am using Power query by creating a function with start and end date, passing the start and end date of the project into the parameter, expanding the column. Expressions in the query:

Duration.Days([Project End Date] - [Project Start Date]) +1 – this is to get number of days
[#“Total Duration(Hours)”] / [Days] * [fDay] – to get monthly duration in hours.

I am not sure where I missed it.
I anticipate your help

File attached
Project Hours Split.pbix (1.1 MB)

Hi @upwardD,

You didn’t provide the XLSX / CSV data… Nice work on the custom function though :+1:

But there’s a difference between the two tables, this is your Allocation

And this is your Project Data

As far as I understand it, that’s equal to the difference you’re trying to resolve…
I hope this is helpful

2 Likes

Thank you @Melissa , just noticed that now