Search code examples
ssasdata-warehousedaxssas-tabular

Sum values on date table where one column equals a selected value from another

I have a DimDate table that has a Billable Day Portion field that can be between 0 and 1. For each day that's in the current Bonus Period I want to multiple that Day Portion by 10, and then return the total sum.

To find out what Bonus Period we're in, I return ContinuousBonusPeriod where the date equals today:

Current Continuous Bonus Period:= CALCULATE(MAX(DimDate[ContinuousBonusPeriod]), FILTER(DimDate, DimDate[DateKey] = TODAY()))

I can see in the measure display this is correctly coming back as Bonus Period 1. However, when I then use ContinuousBonusPeriod in the measure to determine the number of days in the current period, it only returns 10, 1 day multiplied by the static 10.

Billable Hours This Period:= CALCULATE(SUMX(DimDate, DimDate[Billable Day Portion] * 10), FILTER(DimDate, DimDate[ContinuousBonusPeriod] = [Current Continuous Bonus Period]))

It appears to be only counting today's DimDate record instead of all the records whereContinuousBonusPeriod = 'Bonus Period 1' as I'd expect.

Solution

  • I needed to make sure no existing filter was applied to the DimDate table when calculating the Current Continuous Bonus Period:

    Current Continuous Bonus Period:= CALCULATE(MAX(DimDate[ContinuousBonusPeriod]), FILTER(ALL(DimDate), DimDate[DateKey] = TODAY()))

    (Notice the ALL() statement)