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
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?
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.
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
- Anonymous5 years agoNot applicableAs for the True/False flag: YES, YOU CAN MULTIPLY BOOLS WITH NUMBERS and they'll be treated then as 1 and 0, respectively. In DAX, there's no way to tell (in a visual) whether you're on the total row or not. If, say, you choose months instead of days to be displayed on rows and the total will therefore have all the months in a year, you will display the cumulatives for only those months where the last day has the [Own?] flag set to True. Otherwise, you'd display BLANK. Same goes for the total - whether you display a figure or not depends on what there is in the last day. This would be very messy. On the other hand, if you say "If there is more than 1 day visible in the context, display the cumulative until the last day in the period that has the flag set to True", you will almost get what you want apart from the fact that there may be a BLANK total whereas most of the months will have a figure.... The way you want to calculate your measure, or rather display it, is hard to make consistent. I'd think about a different way: calculate the cumulative for ANY day but use the flag to only include the "True" days. This way, you'd easily implement a total that would be consistent with your detail rows.