Creating a Sum Based on Month

[BJP99]

Hi,

I have budget data by month.  How do I go about creating a field that would add Months 1-8 of said data, ignoring the other 4 months.  This data being budget data does not have actual dates tied to it, just Month Names (Jan, Feb, Mar ect) or Number (1,2,3, ect).  Thanks.

[Anonymous]

Hi @BJP99 

Based on your needs, I have created the following table.

Then create a calculate column:

(Jan - Aug) = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),MONTH('Table'[Date])>=1 && MONTH('Table'[Date])<=8))

Drag the corresponding field to the matrix visual.

Drag to hide unwanted columns:
Solved: Matrix - Hide a column in visual but consider in c... - Microsoft Fabric Community

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BJP99]

Hi, thank you for putting this together.  Unfortunately it is giving me the same value for everything.  

[Anonymous · Accepted solution]

Hi @BJP99 

Please try the following Dax:

(Jan - Aug) = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),MONTH('Table'[Date])>=1 && MONTH('Table'[Date])<=8 && 'Table'[Account]=EARLIER('Table'[Account])))

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nonessential]

Hi, try 

Jan - Aug =

CALCULATE(

    SUM('Table'[Value]),

    FILTER('Table','Table'[Month] IN {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"}))

Or if you have Month Numbers;

Jan - Aug =

CALCULATE(

    SUM('Table'[Value]),

   'Table'[Month(number)>=1&&'Table'[Month(number)<=8]))

[Nonessential]

Do you want a column that shows the sum of 'Amount' for months Jan-Aug in every cell?

Or do you want a Column for Jan, a Column for Feb etc? Either way, the columns would just have the same number all the way down in every cell....a Measure would be much more efficient.

How do you intend to display the calculation in your report?

[BJP99]

I want a column that has Months 1-8 Added together.  I currently and viewing the data in a table that shows Months as columns and Accounts as the Rows.  The Budgets come in for each of the accounts per month in this view.  the source data is set up as row for each month with a value by account. 

This is what I want my tabke to look like:

Account      Jan     Feb   Mar Apr   May   Jun  Jul  Aug  Sep  Oct  Nov   Dec   (Jan - Aug)   FY

Acct 1         1         1.2     1.1  1.4     1       1.5   1.1   1.3    1.8    1.5   1.6   2.3           9.6        16.8

Acct 2         3    

Acct 3        2.1

[Nonessential]

Do you want to create a measure or a calculated column? I assume you want a measure, if so you could do something like; Budget (Jan-Aug) = Calculate([Total Budget]), MonthNumber IN (1,2,3,4,5,6,7,8)). For this to work you would need the 'Total Budget' Measure which would just be a SUM of the Column you are performing the calculation on.

[BJP99]

I think I would prefer a calculated column for this.  The Fields I have that would be based on this are Month and Amount.  Month has the Month Names, and Amount has the Amounts associated with this.