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

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

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