Sums by Month and Category

[Anonymous]

Hello,

I'm looking to summarize my data by getting monthly transaction sums (including the year as well, i.e. MM/YY) and filtering them by individual account numbers. Below is an example:

Date		Account Number		Transaction Amount
1/20/2020		1		10
1/21/2020		1		30
2/4/2020		1		20
2/9/2020		1		10
2/15/2020		1		20
1/20/2020		2		30
1/21/2020		2		30
2/4/2020		2		10
2/9/2020		2		20
2/15/2020		2		10
2/20/2020		2		20

I want to take that and make it into this:

Transaction Month		Account Number		Monthly Sum
Jan-20		1		40
Feb-20		1		50
Jan-20		2		60
Feb-20		2		60

I converted the dates to the format I want with this code: 

Transaction Month = MONTH(Table[Date]) & "/" & YEAR(Table[Date])

Then got the total monthly sum:

Total Monthly Sum = CALCULATE(sum(Table[Transaction Amount]),ALLEXCEPT(Table, Table[Transaction Month]))

Now I'm trying to figure out how to filter the total monthly sum by individual account numbers. Just as a note - I need this to be a calculated column as well because I'll want to identify accounts that surpass individual account monthly spending limits. Can anyone help me with this?

Thanks so much!

[v-yangliu-msft]

Hi  @ amgt , 

Here are the steps you can follow： 

1. Create a measure. 

Month = FORMAT('Table'[Date],"MMMM") 

 2. Create a Calculation Table. 

Table 3 =  
SUMMARIZE('Table','Table'[month],'Table'[Account Number], 
"Transaction Month",SUM('Table'[Transaction Amount])) 

 3. Result. 

You can downloaded PBIX file from here. 

Best Regards, 

Liu Yang 

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

[MattAllington]

The trouble with your approach is it is one off - converting the dates to months. Power BI is designed to work with a star Schema. You should create and load a calendar table and an account table. 
https://exceleratorbi.com.au/power-pivot-calendar-tables/

https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

[Anonymous]

Thanks for the reply. Any insight into how to apply a calendar and account table so that I can create a calculated column for the monthly sum by account number? The end goal is to identify monthly account spend that exceeds individual account monthly spending limits.

[MattAllington]

You can use Power Query to create a calendar table.  

https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/