Calculation using previous day value

[mohamedh]

Hi Is there a way to do a calucation whereby i can add my previous day value to my current day calculation?

eg. previous day closing stock + opening stock current day =

Thanks

[v-huizhn-msft · Accepted solution]

Hi @mohamedh,

Of course, you should lookup the previous day first, then calcualte it using calculated column.

I create sample data table.

Create a column ranked by date, then lookup the previous day value based on the rank colculated column.

Rank = RANKX(Test,Test[Date],,ASC)

previous day closing value = LOOKUPVALUE(Test[closing stock],Test[Rank],Test[Rank]-1)

Finally add the current day value and previous day value together. Please see the formula and screenshot below.

previous-day-closing+opening-stock-current-day = Test[closing stock]+Test[previous day closing value]

If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia

[CheenuSing · Accepted solution]

Hi @mohamedh

The link

http://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/

may be helpful. Check it out. If it solves your issue please give KUDOS.

Cheers

CheenuSing

[v-huizhn-msft · Accepted solution]

Hi @mohamedh,

Of course, you should lookup the previous day first, then calcualte it using calculated column.

I create sample data table.

Create a column ranked by date, then lookup the previous day value based on the rank colculated column.

Rank = RANKX(Test,Test[Date],,ASC)

previous day closing value = LOOKUPVALUE(Test[closing stock],Test[Rank],Test[Rank]-1)

Finally add the current day value and previous day value together. Please see the formula and screenshot below.

previous-day-closing+opening-stock-current-day = Test[closing stock]+Test[previous day closing value]

If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia