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,

Please check if one of the following DAX returns your expected result.

Cumulative_actual = CALCULATE(DISTINCTCOUNT([Actual Monthly Cost]);FILTER(ALLEXCEPT(SHEETNAME;SHEET NAME [ProjectCode]); SHEET DATE[Month number] <=MAX(SHEET [Month number])))
Cumulative_actual = CALCULATE(DISTINCTCOUNT([Actual Monthly Cost]),FILTER(ALLEXCEPT(SHEETNAME,SHEET NAME [ProjectCode]),SHEET DATE[Month number] <=MAX(SHEET [Month number])))



Regards,
Lydia