topic Re: Applying factor to monthly totals in DAX Commands and Tips

Applying factor to monthly totals

metcala — Fri, 27 Jan 2023 13:38:46 GMT

Hi all

I'm just after a bit of guidance in how I can apportion out a full year plan against each month and apply a factor for down time in certain months. It also needs to ensure new starters are factored in i.e. don't get a full year plan and anyone working part time is allocated the correct apportionment. The data is structured as below.

Data Structure

Plan Table

Employee ID

Activity

Days allocated

Staffing Table

Employee ID

Employee name

Start Date

End Date

FTE

Proposed Solution

Slicer by Employee Name

Activity	April	May	June	July	August	September	etc
Corporate
Development
Etc

Current Ideas

Measures

12 separate measures for each month e.g.

April = IF('Staffing'[Start]<DATE(2023,4,1) && 'Staffing'[End]>DATE(2023,4,30, SUMX('Plans','Plans'[Days]*0.065), 0)

This currently isn't working and wanted to get this sorted before looking at FTE. Assume I would just added a nested if to consider FTE but if there is a better way any pointers would really be appreciated.

Assumptions

6.5% during April and December and 8.7% for all other months

Thanks in advance for any guidance!

Re: Applying factor to monthly totals

metcala — Mon, 30 Jan 2023 10:36:57 GMT

Not sure if this will be of use to anyone but think I managed to solve part of the problem, definitely not the most elegant solution....

Created a Calculated Column on the Staffing for number of months present. Then created a measure for each month e.g.

April = ((12 / SUM('Staffing'[MonthsPresent])) * 0.065) * SUM('Plans'[Days])

Still doesn't resolve FTE but thought I'd give an update. Another issue I do have is the factor is applying to all Activities including leave. Ideally, I would want the factor to apply to all Activities except leave and leave adjusted to make the days per month balance but struggling a bit with that.

Re: Applying factor to monthly totals

metcala — Mon, 30 Jan 2023 12:14:31 GMT

Further update....

In order to address the adjustment to leave I have tweaked the measure above

April = CALCULATE SUMX('Staffing',((12 / SUM('Staffing'[MonthsPresent])) * 0.065) * SUM('Plans'[Days])), FILTER('Plans','Plans'[Activity]<>"Leave") )

I have then just added a new measure for leave (21.67 is days expected per month)

April Leave = 21.67 - 'Plans'[April]

I'm concerned this is a very inflexible and that I currently have 24 separate measures. I imagine there is a much better way to do this.

I am also really struggling with how I can adjust the month factor based on other criteria such as 'Staffing' Location.

Any help would be really appreciated!