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]))

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Bryony,

Could you please share sample data of your table and expected result through online service such as OneDrive?

Regards,
Lydia

Bryony's avatar
Bryony
Frequent Visitor
8 years ago

Hi,

 

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

  • 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]))