Forum Discussion
Helper IHow to spread values across start date and end dates
Hi Team,
I have a table with the following details. And I need to spread the FTE details across the months between the start date and end date for each staff member. Please refer to the desired output table.
Table Details:
| Staff Name | Project | FTE Allocated | Start date | End date |
| Sanjay | AML | 0.5 | 1-Feb-22 | 31-Dec-22 |
| Abir | FIFA | 0.7 | 3-Feb-22 | 18-Nov-22 |
| kapil | FIFA | 1 | 6-Feb-22 | 31-Aug-23 |
| Sanjay | FIFA | 0.5 | 7-Feb-22 | 31-May-22 |
| Abir | AML | 0.2 | 8-Feb-22 | 1-Mar-22 |
| Abir | ISTA | 0.1 | 5-Feb-22 | 31-May-23 |
Desired Output:
| Months | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec |
| Sanjay | 0 | 1 | 1 | 1 | 1 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 |
| Abir | 0 | 1 | 1 | 0.8 | 0.8 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 |
| Kapil | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
I cannot seem to get anything to work, any advice would be appreciated.
Hi amitchandak , Many thanks for sharing the blog. It is really helpful. I manage to spread the values but for me, i only need to spread the values and am not be required to split or divide. Is there any way I can spread the FTE values based on start and end dates? I have copied down my measure. Please let me know your thoughts
My measure :
Planned FTE = CALCULATE(SUMX(SUMMARIZE(FILTER(CROSSJOIN('FTE','Datetable'),'Datetable'[Date]>= (FTE'[Check Start date]) && 'Datetable'[Date]<=('FTE'[End Date])),'FTE'[Staff Name],Datetable[Date],'FTE'[FTE],'FTE'[Check Start date],' FTE'[End Date]),DIVIDE('FTE'[FTE],DATEDIFF('FTE'[Check Start date],'FTE'[End Date],DAY)+1)))
2 Replies
- amitchandak
Super User
Arun_BIHi amitchandak , Many thanks for sharing the blog. It is really helpful. I manage to spread the values but for me, i only need to spread the values and am not be required to split or divide. Is there any way I can spread the FTE values based on start and end dates? I have copied down my measure. Please let me know your thoughts
My measure :
Planned FTE = CALCULATE(SUMX(SUMMARIZE(FILTER(CROSSJOIN('FTE','Datetable'),'Datetable'[Date]>= (FTE'[Check Start date]) && 'Datetable'[Date]<=('FTE'[End Date])),'FTE'[Staff Name],Datetable[Date],'FTE'[FTE],'FTE'[Check Start date],' FTE'[End Date]),DIVIDE('FTE'[FTE],DATEDIFF('FTE'[Check Start date],'FTE'[End Date],DAY)+1)))
