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 answ...
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

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.