Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 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 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's avatar
    AllisonKennedy
    5 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])

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 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
      5 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 
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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