Forum Discussion
Lookup DateKey from another column's running total?
Try adding an ALL() to the base table for the filter as below. Seems to work. Check out if it is so and then we can discuss what was at play.
Working Days Given := CALCULATE ( SUM ( dCalendar[Workday] ), FILTER ( ALL(dCalendar), dCalendar[Date] >= MIN ( fCommTime[ActualStart] ) && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] ) && dCalendar[Workday] = 1 ) ) - SUM ( fCommTime[Days Lost] )No worries. glad it helped.
Your code for the measure:
Working Days Given := CALCULATE ( SUM ( dCalendar[Workday] ), FILTER ( dCalendar, dCalendar[Date] >= MIN ( fCommTime[ActualStart] ) && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] ) && dCalendar[Workday] = 1 ) ) - SUM ( fCommTime[Days Lost] )When we invoke this measure within the other piece of code, we have a row context from the ADDCOLUMNS. As discussed earlier, I was afraid the context transition would play unwanted tricks. When I initially saw your code, though, it seemed fine because you are using the whole dCalendar table as base table for your filtering operation. That should be enough to override the effects of context transition. BUT, and here comes the interesting part, every time a measure is invoked, the engine wraps the measure in a CALCULATE. You probably are aware of that. So what we effectively have when we call your measure is:
CALCULATE ( CALCULATE ( SUM ( dCalendar[Workday] ), FILTER ( dCalendar, dCalendar[Date] >= MIN ( fCommTime[ActualStart] ) && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] ) && dCalendar[Workday] = 1 ) ) - SUM ( fCommTime[Days Lost] ) )The outermost CALCULATE does not have filter arguments and the filter resulting from context transition is applied fully. That filter is the current row of the table (the ADDCOLUMNS table), as you know. Then when the engine executes the inner CALCULATE we have that row as filter and that is applied directly to dCalendar in
FILTER(dCalendar;....)
The base table for the filter operation is just that one row instead of the full table that we would want. That is why you need the ALL( ).
Does that help?
and one thing that we haven't commented on but caught my attention from the beginning: why do you have the 'Productivity' column in your date table, dCalendar?
AlB,
Good question. I am working on a large project in Asia that is made up of smaller projects (modules) where we have to do some commissisoning one each module after it is built. We get a forecasted or actual start and end date to do commissioning on each module as a given. Each module has a planned commissioning duration which right now usual exceeds the given window that we are given. So I need to answer a few questions.
1. In that given window how much uncomplete work would I have left over for each module? I compute this as the
( [NeededWorkWorkdays]- [WorkingDaysGiven] ) / [NeededWorkWorkDays] * [Man-hours to be worked]...
2. If there is going to be work left over, by what date in the future could we complewte it? That/s where you helped me sum working days using the ADDCOLUMNS function. I pull the calendar table fillter based on dates after the end date I'm given and using a running total I figure out how many working days I need to accumulate and return that date.
That was the summing of workdays part. The zeros represent sundays and public holidays. Next came the productivity factors summation.
Since we are trying to complete this left over work as quickly as possible, our team has been working overtime 1.4 producitfy factor instead of just 1.0 which represent the assumed working hours baseline. Then starting certain dates, we are forecasting a partial night shfit to start and give the dayshift guys a break from overtime work ( 1.6 productivyt factor) and then a full night**bleep** at a later date which we assume will only be 80% as productiy as the day shift so you would have 1.8 as that day's productivy. So the dates which these changes in producity happen apply across all modules. So I imported them from an excel table, merged these factors onto the calendar table using power query by matching dates and filled downwards. Then any remaining null values got replaced with 1.0 (default)
By summing the daily producity factors from the calendar table filtered for working days only, I can answer the same questions as above, for the nightshift scenario: How much work will be leftover and if given an extension, up until which date I would need it. This would help us forecast when we actually need nightshift to complete the work.
Since the productiy factor is an assumption that depends on date only and is to be referenced in 4 different calculations for each module, it made sense to have it in the Calendar table. One possible improvement I might do next is adjust producity factors based on how many simulaneous modules are available to work on on a given date, but this is good enought for now. Perhaps there would have been a more efficent way of doing this, but I'm reactiving to what I have and it has been a fun experience for me to start learning DAX, powerquery, and Power Bi.
Thank you for all your help!