Filtering a calculated total

[alt731]

I have a table of data that looks like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]BType[/TD]
[TD]Qty[/TD]
[TD]TypeTotal[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2016-06-01[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2016-06-01[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2016-07-20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2016-08-28[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2016-06-20[/TD]
[/TR]
</tbody>[/TABLE]




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?

 

[akice]

Calculated columns are computed at data refresh/load time not at run time. So even if you put a filter on 'Date', the column is not recomputed. Only measures are recomputed at run time. How your measure function is written depends on what you want your pivot to look like.