Replicate excel formula - recursive substraction

[Anonymous]

Hi there. I am trying to replicate excel formula in power BI DAX. I have date column(A) with date, progress column(B) with integer, left column(C) with integer. This meant to be project progress monitoring tool. So the team is starting with 100k instances to process and on each day they do certain progress, so in the cell C2 = 100000-B2,but starting from C3 = C2 - B3, C4 = C3 - B4 and so on...

Help will be appreciated.

Thanks,

Michal

[AllisonKennedy · Accepted solution]

You can do this by calculating a running total or cumulative sum. Then just do 100000- running total.

So for your example:
Remaining instances = 100000- SUMX(FILTER(ALL(Table), Table[Date] >= EARLIER(Table[Date]) ), Table[left])

[Anonymous]

Thanks a lot! That was exactly what i was needing. I only changed 

aggregate_instances_by_date[last_updated] >= EARLIER(aggregate_instances_by_date[last_updated] to:

aggregate_instances_by_date[last_updated] <= EARLIER(aggregate_instances_by_date[last_updated]

so it is the right chronological way 

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.