Writing measure based on other measures

[clubspec]

Hi Experts,

I have 3 measures like you see from below picture:

1. Workload in hours (sum of total hours for each department in week number e.g. there is 468.37 hours of workload for Trimming department in week 29), this measure lives in Workload Table.
2. Capacity (sum of total capacity hours for each department in a week e.g. the capacity of Trimming department in week 29 is 200.66 hours, this measure lives in the Capacity Table.
3. Workload (is a measure simply using Workload in hours / Capacity) so I know there is equivalent 2.33 weeks of workload in week 29 for Trimming department and therefore we need to increase capacity to catch up.

Now I want to create the 4th measure = if the calculated workload is great than 1 (week) then automatically roll to the following week.  e.g. the Trimming department workload in week 29 will be capped at 1 and the rest 1.33 (2.33 - 1 = 1.33) will be moved to the following week.

The total workload should be 3.68 weeks to complete (2.33+0.36+0.39+0.33+0.11+0.1+0.02+0.03+0.01 = 3.68 weeks)

chart from the 4th measure

Thank you,

Ray

[d_gosbell · Accepted solution]

You could do this by calculating the Life Time to Date Workload from the first week and then compare that to the running number of weeks. This produces the output in the bottom table below (ignore the total for the time being, I don't know if there is a meaningful way of calculating the capped workload at the total level - If you do have a requirement for calculating the total "capped workload" reply back letting me know how this should work and I'd be happy to update the measure)

I used the following 2 calculations to achieve the output above

LTD Workload = SUMX(FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date])),[Workload])

Capped Workload = 
VAR _maxDate = CALCULATE(max('Date'[Date]), FILTER(VALUES('Date'[Date]), NOT( ISBLANK([LTD Workload]))))
VAR _minDate = CALCULATE(min('Date'[Date]), FILTER(ALL('Date'[Date]), NOT( ISBLANK([Workload]))))
VAR _weeksToDate = CALCULATETABLE(DISTINCT('Date'[Week of Year]), FILTER(ALL('Date'[Date]), 'Date'[Date] >= _minDate && 'Date'[Date] <= _maxDate))
VAR _runningWeekCount = COUNTROWS(_weeksToDate)
VAR _runningWorkload = [LTD Workload] // SUMX(_weeksToDate, [Workload])
RETURN IF( _runningWorkload > _runningWeekCount, 1,IF(_runningWeekCount >=1 && _runningWeekCount-1 < _runningWorkload, _runningWorkload - (_runningWeekCount-1)))

[clubspec]

Thank you d_gosbell,

I see the idea of the measures you wrote below and I had to adjust a little bit to suit my case as relationship between my tables are not connected by 'Date' but the weeknum because my Capacity Table only has weeknum colum and the capacity (they don't breakdown to daily capacity for me).

At the end I manage to use another graph to show what I wanna show so it is all good.

Thank you again for your reply and look into my problem I really appreciate it.

Cheers,

Ray