Lookup DateKey from another column's running total?

palvarez83

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] )

palvarez83

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?