Forum Discussion

CanadaMGB's avatar
CanadaMGB
Frequent Visitor
9 years ago
Solved

Running Total that includes calculations based on prior amount

Is it possible to produce a running total in DAX that includes calculations based off the previous running total balance? My thinking is no, but after researching it I couldn't find a definitive answer, and so I'm throwing it out there in case I'm completely out to lunch and it is possible.

 

As a simplified example,  the image below from excel shows a running count of customers based on the estimated % of new and lost (churn) customers each month. The question posed to me is whether i could produce this in Power BI if given only an opening amount, New % and Churn %. The New and Churn amount are calculated as the product of that month's % times the previous month's running total, and I cannot come up with a way of calculating that without causing circular dependancy errors.

 

 

 

Thanks in advance!

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    CanadaMGB,

     

    I have tested it on my local by using the sample data below.

    Then create a calculated column
    Rate = IF(ISBLANK(Table2[Churn%]),400,1-Table2[Churn%]+Table2[New%])

    And create a measure
    Measure 2 = CALCULATE(PRODUCTX(Table2,Table2[Rate]),FILTER(ALL(Table2),Table2[Date]<=MAX(Table2[Date])))

     

    Result

     

    Regards,

    Charlie Liao

7 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    CanadaMGB,

     

    I have tested it on my local by using the sample data below.

    Then create a calculated column
    Rate = IF(ISBLANK(Table2[Churn%]),400,1-Table2[Churn%]+Table2[New%])

    And create a measure
    Measure 2 = CALCULATE(PRODUCTX(Table2,Table2[Rate]),FILTER(ALL(Table2),Table2[Date]<=MAX(Table2[Date])))

     

    Result

     

    Regards,

    Charlie Liao

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    9 years ago

    I'm not sure if this can be done or not.  If so, it would certainly be complex.  But it seems to me that this requirement is a bit back to front anyway.  The % churn and new numbers presumably are generated based on the actual numbers of new and churn, not the other way around.  Stated another way, it seems to me that somewhere there must be the raw data of the number of new and churn customers that were used to generate these percentages.  This is what would ideally be loaded.

    • CanadaMGB's avatar
      CanadaMGB
      Frequent Visitor
      9 years ago

      thanks MattAllington, good to know I'm at least not missing something obvious. And yes I definitely oversimplified my example, as you pointed out it is a bit backwards thinking as stated. The actual data I'm working with is part of a forecast, hence why the rates are provided and not the raw data on the counts.

       

      By the way, attended your session on evaluation context at Data Insights last week, very helpful!

      • MattAllington's avatar
        MattAllington
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Glad ,my session was helpful.  I haven't given up on your problem being doable.  Maybe if I get bored doing my real work this week I will take another look.  I am thinking a calculated column would be better in this example.  You may remember I said "don't use a calc column unless you know why".  In this case a measure would be quite heavy at run time as each calculation would become incrementally longer to execute.  Precalculating the numbers and storing in a table would seem to make sense to me.