Forum Discussion

kcdistiller's avatar
kcdistiller
Frequent Visitor
9 years ago

Calculate Running Total Using Input from Another Calculation (Measure)

There are many solutions to calculate a running total based on dates or counts within a table, but I have not found a way to create a running total measure using input from another measure. Consider ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi kcdistiller

 

It is possible to do it without a calendar table also.

 

I am assuming your UniqueID ( Column A) and Medication Data ( Column B) are coming from the same table.

 

Try this for column D , where you need the running total of column C

 

Define a measure RunningTotC 

 

RunningTotC =  Calculate([measureCoumnC],
                                                        FIlter(

                                                                    ALL(SourceDataTable),
                                                                          (SourceDataTable[ColumnA])<=MAX(SourceDataTable[CoulmnA])
                                                                  )

                                                                     )

 

 

The highlighted area above can also be changed to take a concatenation of Column A and Column B

as 

  ( SourceDataTable[ColumnA]&"-"&SourceData[ColumnB]<=MAX(SourceDataTable[ColumnA]&"-"&SourceData[ColumnB])

 

If this works for you please accept it as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

  • kcdistiller's avatar
    kcdistiller
    Frequent Visitor
    9 years ago

    Your suggestion calculates something, but not what is expected. If there is a way to use 'EARLIER' in place of 'MAX' in your recommended code, I think it would work. However, I get an error saying that "'EARLIER' refers to a row context which dosen't exist'. This is likely due to 'Percentage' being a measure, not an actual value in a table. If I change the measure to a calculated column, I lose the ability to update the value based on slicers. Ideally, the code would probably look like

     

    RunningTotC = CALCULATE([Percentage],Filter(ALL('My Table'),('My Table'[Percentage])>=EARLIER('My Table'[Percentage])))

    where 'Percentage' is the result of the measure in Column C, but 'EARLIER' does not work with measures.