Forum Discussion
Sum per Month per Location
Hello fellow forum friends,
I am a noob at DAX and PowerBI and I'm trying to solve the following problem:
Table1 has the following columns: Date, Total, and Location.
I have many entries per day and location.
I want to add an extra column called 'Total per month per location', that would add an entry with the sum of the values in Total for that month on that location.
Would look like this:
| Total | Date | Location | TotalPerMonthPerLocation |
| 15 | 01-01-18 | A | 25 |
| 10 | 02-01-18 | A | 25 |
| 5 | 01-01-18 | B | 15 |
| 10 | 02-01-18 | B | 15 |
| 15 | 01-02-18 | A | 20 |
| 5 | 02-02-18 | A | 20 |
| 20 | 01-02-18 | B | 35 |
| 15 | 02-02-18 | B | 35 |
Right now I managed to have a column that shows the sum of Total on that month, but I also want it to discriminate it via Location.
Right now I have this:
| Total | Date | Location | TotalPerMonthPerLocation |
| 15 | 01-01-18 | A | 40 |
| 10 | 02-01-18 | A | 40 |
| 5 | 01-01-18 | B | 40 |
| 10 | 02-01-18 | B | 40 |
| 15 | 01-02-18 | A | 55 |
| 5 | 02-02-18 | A | 55 |
| 20 | 01-02-18 | B | 55 |
| 15 | 02-02-18 | B | 55 |
My current query is
TotalPerMonth = CALCULATE(SUM(Table1[Total]),FILTER(Table1,Table1[Date]= EARLIER(Table1[Date])))
Can anyone help me alter the query so I can achieve what I'm trying to do?
Thank you <3
Anonymous Please try this as a new column
TotalPerMonthPerLocation = CALCULATE(SUM(Test192LkpSum[Total]),FILTER(Test192LkpSum, MONTH(Test192LkpSum[Date]) = MONTH(EARLIER(Test192LkpSum[Date])) && Test192LkpSum[Location] = EARLIER(Test192LkpSum[Location])))
2 Replies
- PattemManohar
Community Champion
Anonymous Please try this as a new column
TotalPerMonthPerLocation = CALCULATE(SUM(Test192LkpSum[Total]),FILTER(Test192LkpSum, MONTH(Test192LkpSum[Date]) = MONTH(EARLIER(Test192LkpSum[Date])) && Test192LkpSum[Location] = EARLIER(Test192LkpSum[Location])))