Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replicate excel formula - recursive substraction

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 moni...
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago
    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])
AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago
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's avatar
Anonymous
Not applicable
6 years ago

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