This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 36%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ED%3C/text%3E%3C/svg%3E)
I have a metric which is a Cumulative Sum of Items based on the number of months since a batch of items was created. Incremental Items are the number of items added to the batch each month with month zero being the initial month. So batch 899486 had 329 items during it's initial month (Cumulative 329), 2 after one month (Cumulative 331) and zero month 2 (Cumulative 331). Incremental Item is a field in the database and is a simple sum. Cumulative_Items is defined as
Cumulative_Items:=
CALCULATE ([Incremental_Items],
FILTER( ALLSELECTED('MonthsOutstanding'[MonthsOutstanding]), ' MonthsOutstanding '[MonthsOutstanding] <= max(' MonthsOutstanding '[MonthsOutstanding]))
)
The issue is that the batches shown in red do not have entries for months 1 and 2 but i'm getting crossjoin of some type. The problem is the subtotal for months one and two are wrong. I use the total in a downstream calculation. For instance, the total for month 2 should be 839. Can anyone see what I'm missing? MonthOutstanding is a dimension and 1:M to the fact table. Same way with batch.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 95%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
That's no moon, sorry, no crossjoin. It's just your cumulative calculation doing its thing, that is, summing all the values from month 0 to current. Apparently you want the calculation to ignore batches that haven't been added to in the current month. One things that stands out here is that in your data, a batch can have either NULL or 0 items added, and these cases seem to be treated differently. A subtle problem is what you want your grand total to be. Your measure needs to be coded differently to fulfill these requirements, but first you'll have to decide what logic you want to implement, exactly.
You are correct. It's perfectly valid for a batch to have 0 items added for a particular month AND to have no records (NULL) at all for a later month. I want to sum the 0's and exclude the nulls. I've looked at ISBLANK type of functions but it does not work. Can an IF statement be added to account for this?
My only other alternative is to do the cumulative calc in the database itself. I'd rather avoid that since it's a very expense calculation on 100s of millions of rows.
ISBLANK does work, and yes, it is usually paired with an IF in actual usage. As of this comment, you seem to be missing the need for an iterator, probably SUMX, to implement your subtotal logic that varies between individual constituents.