Calculation - Continuance

[Anonymous]

I have been asked to perform a calculation to show continuance probabilities which uses a multiplication of two measures in the prior month, one of which is the value I'm trying to caclulate.  In .xls it would be simple to refer to the row above and run the calculation.  However, in power bi I'm struggling to work out how to do this.

It would be similar to this in Column A*Column B, where column B is the column being calculated. In the image E8 equals D7*D8.  I'm wondering if the PRIORMONTH Functioin would do it, but feel that it will throw a circular reference.

Any help greatly appreciated.

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Are you referring to  function 'PREVIOUSMONTH' ? It is a time intelligent function, you need to ensure that you have a continuous date table when using it.

As mentioned by amitchandak ,you can also create a Index column in Advanced Editor.

Then you can try formula as below :

Column2 = CALCULATE(MAX('Table'[Column1]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))

continuance probabilities = 'Table'[Column1]*'Table'[Column2]

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Anonymous , How you want prior month or depend on you data.

Like if you can add a index column in power query  then last row values

A DAX new column

new column = maxx(filter(Table, [Index] =earlier([Index]) -1) ,[ColumnA])

How to add an index column https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi