Power BI Sum by Category and Month

0
votes

I have a Power BI/DAX question. 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. Here is an example:

table 1

I want to take that and make it into this:

table 2

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!

1
powerbidax

1 Answers

2
votes

When working with calendar dates, it pays to have a calendar table linked to the transaction table. In the calendar table you will have each date, from the start date of your relevant time period to the end of the time period relevant to your data. The columns of the calendar table can then contain calculations on that date like month number, month name, year, year-month key, transaction month (as the first day of the month for the date in that row), etc.

Next, connect the two tables in the data model by dragging the transaction date to the calendar date column.

Now you can build charts and report tables that group data by month without writing any complicated DAX. Just pull the field "transaction month" from the calendar table and the Total Sum measure from the transaction table into the field well of the visual.

That's what Power BI is all about.