Forum Discussion

Bryony's avatar
Bryony
Frequent Visitor
8 years ago
Solved

Calculating cumulative Months by project code

Hi There,   I am trying to calculate a cumulative monthly total (adding each months total to the previous month) and filtered by relevant project codes. I have the following headings which i am usi...
  • Bryony's avatar
    Bryony
    8 years ago

    Hi,

     

    I have now managed to create the formulas :) Thank you

  • Bryony's avatar
    Bryony
    8 years ago

     

    The below formula seems to work;  I created a sum column calculating figures by month, then created another colum to filter by project;

     

    Monthly Sum = CALCULATE(SUM(Financials[Actual Monthly Cost]),FILTER(ALL(Financials),Financials[Date] <=TODAY()))

     

    By Project = CALCULATE(SUM(Financials[Actual Monthly Cost]),ALLEXCEPT(Financials,Financials[ProjectCode],Financials[Monthly Sum]))

     

TomMartens's avatar
TomMartens
Super User
8 years ago

Hey Bryony,

 

this is a great!

 

Can you please post your solution and mark it as answer, this will help all the other community members (including me) a lot!!

 

Thanks

Tom

Bryony's avatar
Bryony
Frequent Visitor
8 years ago

 

The below formula seems to work;  I created a sum column calculating figures by month, then created another colum to filter by project;

 

Monthly Sum = CALCULATE(SUM(Financials[Actual Monthly Cost]),FILTER(ALL(Financials),Financials[Date] <=TODAY()))

 

By Project = CALCULATE(SUM(Financials[Actual Monthly Cost]),ALLEXCEPT(Financials,Financials[ProjectCode],Financials[Monthly Sum]))