cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Bryony Frequent Visitor
Frequent Visitor

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 using;

 

Project code / Actual Monthly Cost / Date / Month Number

 

I have been trying the use the below formula however it doesnt seem to be working;

 

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

 

If anyone is able to help that would be much appreciated. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Bryony Frequent Visitor
Frequent Visitor

Re: Calculating cumulative Months by project code

Hi,

 

I have now managed to create the formulas Smiley Happy Thank you

Bryony Frequent Visitor
Frequent Visitor

Re: Calculating cumulative Months by project code

 

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

 

9 REPLIES 9
Super User
Super User

Re: Calculating cumulative Months by project code

Hey,

 

as far as I can see the formula you provided will not work due to missing parnetheses.

 

I would recommend that you create some sample data, upload the pbix to onedrive or dropbox and share the link.

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Bryony Frequent Visitor
Frequent Visitor

Re: Calculating cumulative Months by project code

Many thanks Tom. I have just sent the link to your inbox. 

Moderator v-yuezhe-msft
Moderator

Re: Calculating cumulative Months by project code

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Calculating cumulative Months by project code

Hey,

 

thanks for sharing sample data, maybe you might consider to share the link using a normal post and not a private message :-)

 

Nevertheless ...

 

I created a new column that just contains a date datatype using this statement, because your sample data did not contain a column called "Month Number":

A Date Column = 
DATE('Financials'[Year]
    ,SWITCH('Financials'[Month]
        ,"January",1
        ,"February",2
        ,"March",3
        ,"April",4
        ,"May",5
        ,"June",6
        ,"July",7
        ,"August",8
        ,"September",9
        ,"October",10
        ,"November",11
        ,"December",12)
    
    ,1)

After that I created another calculated column, that acumulates the values from the column Actual Monthly Cost:

**bleep** Actual Monthly Cost = 
var currentDate = 'Financials'[A Date Column]
var currentProjectCode = 'Financials'[ProjectCode]
return 
CALCULATE(
    SUM('Financials'[Actual Monthly Cost])
    ,FILTER(ALL('Financials')
        ,Financials[A Date Column] <= currentDate
        //&& 'Financials'[ProjectCode] = currentProjectCode
    )
)

I was'n sure if you wanted this calculation per Projectcode (probable: inspecting the formula you provided). If this is the case, just remove the comment sign // from this line

//&& 'Financials'[ProjectCode] = currentProjectCode

If you do not need the line, you can remove the following lines:

...
var currentProjectCode = 'Financials'[ProjectCode]
...
        //&& 'Financials'[ProjectCode] = currentProjectCode
...

Here is a little screenshot:

image.png

 

As you can see, there is an issue with the February issues, this will be automatically resolved if the acumulation has to happen for each Projectcode separately. If not the calculation will become much more sophisticated, and can't be achieved using a calculated column. And you also have to provide an explanation about the nature of your sample data and provide the expected result that matches your sample data.

 

Regards

Tom

 

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Bryony Frequent Visitor
Frequent Visitor

Re: Calculating cumulative Months by project code

Many thanks for your response. I have managed to calculate a monthly sum of the Actual Monthly Cost now however, still unable to filter the Actual Monthly cost by project code (so currently it is showing the figure as a sum of all projects). I am unable to get the last part of the formula to work; //&& 'Financials'[ProjectCode] = currentProjectCode

 

 

Moderator v-yuezhe-msft
Moderator

Re: Calculating cumulative Months by project code

@Bryony,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Bryony Frequent Visitor
Frequent Visitor

Re: Calculating cumulative Months by project code

Hi,

 

I have now managed to create the formulas Smiley Happy Thank you

Super User
Super User

Re: Calculating cumulative Months by project code

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Bryony Frequent Visitor
Frequent Visitor

Re: Calculating cumulative Months by project code

 

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