Forum Discussion
Sum by Month
Hello Everybody!
I have this table and I want create one measure who SUM the column VALUE by month.
| DATA | VALUE |
| 10/01/2016 | 10 |
| 10/02/2016 | 10 |
| 11/01/2016 | 20 |
| 11/02/2016 | 20 |
| 12/03/2016 | 30 |
| 12/03/2016 | 30 |
So, if I Sum the Dates I have this:
| DATA | VALUE |
| 10/2016 | 20 |
| 11/2016 | 40 |
| 12/2016 | 60 |
If the user filters the date between 10/10/2016 and 11/01/2016 I would like return 60, no matter which day of the month the cursor is applied.
If is between 10/01/2016 and 12/05/2016 I would like return 120
If is just on 11/01/2016 or anything else days of this month, would like return 60.
If the filter contains some month, I would like sum it all days.
Thank you very much!
I solve it
Measure = CALCULATE(SUM(Sheet1[Value]);FILTER(ALL(Sheet1);Sheet1[Value]<=MAX(Sheet1[Value])))
6 Replies
Hi DanJaques
1. Create a calculated column Date_Month = FORMAT(Table1[DATA],"YYYY/MM")
2. Create a Measure Sum_Value = SUM(Table1[VALUE])
Hope this helps.
Thanks
RajHi Anonymous
First, thaks for helping.
This way you told me works, but the dashboard already have a filter by date who is connected with another graphics.
If the data is full and I use your Date_Month, it works perfectly.
But if I change the date I lost my value who actually should be 310(full value January)+580(full value of january) = 890
