I have a table of data that looks like this:
<tbody>
</tbody>
I'm using DAX to create the TypeTotal column above. It is effectively doing a SUMIF, and then producing a total quantity for the specific BType and sticking it in the TypeTotal column.
TypeTotal = CALCULATE(SUM(Query1[Qty]), FILTER(Query1, Query1[BType] = EARLIER (Query1[BType])))
Note that if you total up the Qty for B2 in the table, it comes to 3, which is why TypeTotal is showing 3. This is exactly what I want.
However, I also want the TypeTotal column to be responsive to a date filter.
I can drag in a filter to filter on Date, and lets say I set it to between 2016-07-01 to current. In this case, the row with ID of 2 will be filtered out, which is again, exactly what I want. But the TypeTotal column for ID rows 3 and 4 still displays 3 (i.e. the total for B3 in the whole table) not the total for B3 on the filtered data set. This is not what I want.
How do I go about getting the TypeTotal column to change its total based on the date filter?
ID | BType | Qty | TypeTotal | Date |
1 | B1 | 1 | 1 | 2016-06-01 |
2 | B2 | 1 | 3 | 2016-06-01 |
3 | B2 | 1 | 3 | 2016-07-20 |
4 | B2 | 1 | 3 | 2016-08-28 |
5 | B3 | 1 | 1 | 2016-06-20 |
<tbody>
</tbody>
I'm using DAX to create the TypeTotal column above. It is effectively doing a SUMIF, and then producing a total quantity for the specific BType and sticking it in the TypeTotal column.
TypeTotal = CALCULATE(SUM(Query1[Qty]), FILTER(Query1, Query1[BType] = EARLIER (Query1[BType])))
Note that if you total up the Qty for B2 in the table, it comes to 3, which is why TypeTotal is showing 3. This is exactly what I want.
However, I also want the TypeTotal column to be responsive to a date filter.
I can drag in a filter to filter on Date, and lets say I set it to between 2016-07-01 to current. In this case, the row with ID of 2 will be filtered out, which is again, exactly what I want. But the TypeTotal column for ID rows 3 and 4 still displays 3 (i.e. the total for B3 in the whole table) not the total for B3 on the filtered data set. This is not what I want.
How do I go about getting the TypeTotal column to change its total based on the date filter?