Forum Discussion
Running Total with If
- 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
Question 1: Why would you store the bool flag [Own?] as something else than a True/False flag?
Answer: If I store [Own?] as 1s and 0s, I can multiply [Own?] with the unconditional running total (which I can already do). Then perhaps I can sum them for a particular date. For example, for one date which is 30th June 2020:
Question 2: From what you've said above I understand that when you are saying "cumulative" it means you only want to aggregate from beginning to the current date but only over those dates for which the flag is True? Am I correct?
Answer: Yes, correct.
Question 3: I don't quite get how the total row should work. If the company in question (say, there's only one visible) has the last day visible in the context (which means on the total row) with the flag set to False, you can't display the total because you'd violate the rules of the measure. If you do want to display any kind of total, then you have to modify the rules of your measure. It is to say that the rules of calculation must be consistent and with the current setup and your 'desire' they are not.
My reply: OK, I see. Thanks