Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    5 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