Calculating cumulative Months by project code

[Bryony]

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. 

[Bryony · Accepted solution]

Hi,

I have now managed to create the formulas 🙂 Thank you

[TomMartens]

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:

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

[Bryony]

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

[v-yuezhe-msft]

@Bryony,

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

Regards,
Lydia

[Bryony · Accepted solution]

Hi,

I have now managed to create the formulas 🙂 Thank you

[TomMartens]

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 · Accepted solution]

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

[v-yuezhe-msft]

@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

[TomMartens]

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

[Bryony]

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