Forum Discussion
Anonymous
5 years agoNot applicable
Running Total with If
Hi. I have a measure of running total of payments made for investments over time. I also have an input table that shows when my department owns the investments and when we don't (an "ownership Boolea...
- Anonymous5 years ago
// This will work OK only IFF for each // company there exists AT MOST 1 day // in the Payments table. [Cumul Payment] = var __lastVisiblDate = MAX( Date[Date] ) var __onlyOneCompanyVisible = // Company should be a dimension // that joins to Payments, so please // do yourself a favour and make your // model into a proper star schema. // This code assumes that Company // exists only in your fact table // Payments but this is NOT how it // should be. If you have Company // as a dimension, you'll change the // code below to // HASONEVALUE( Company[CompanyID] ) // instead. HASONEVALUE( Payments[Company] ) var __result = if( __onlyOneCompanyVisible, var __weOwnTheInvestment = CALCULATE( SELECTEDVALUE( Payments[Own?], 0 ) = 1, // This is where you have to make // sure that the assumption from // above is observed. Otherwise, // the value of CALCULATE will always // return False. Date[Date] = __lastVisibleDate ) RETURN if( __weOwnTheInvestment, CALCULATE( SUM( Payments[Payment] ), // Date must be a date table // in the model marked as such // for this to work OK. Date[Date] <= __lastVisibleDate // If you want to only sum up // the payments where [Own?] is 1 // then you have to add as a filter // this condition to this CALCULATE: // Payments[Own?] = 1. // But if you do this, you'll only // get a running total for the // payments that you own, not the // true running total. But maybe this is // what you want... Nevertheless, the code // only displays the total if the // max day for the visible company // has the flag set to 1 regardless // of the method of summation. ) ) ) return __result
Anonymous
5 years agoNot applicable
Please show us a relevant example of what you really want. Thanks.
Anonymous
5 years agoNot applicable
Hi Anonymous (cool nickname btw, very apt). Sure, I'd be happy to. You've probably noticed that I stopped owning the investment A starting 31 Jan 2010 and stopped owning B starting 14 Feb 2010. Therefore in a Matrix, I would like to have a (conditional) running total like this:
However despite using
CALCULATE (
SUM('Payments'[Payments]),
'Date'[Date] <= LastVisibleDate,
'Ownership'[Own?]=1)
)
, the condition " 'Ownership'[Own?]=1 " doesn't have an effect and I get a Matrix like this instead: