cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DanJaques
Regular Visitor

Sum by Month

Hello Everybody!

I have this table and I want create one measure who SUM the column VALUE by month. 

DATAVALUE
10/01/201610
10/02/201610
11/01/201620
11/02/201620
12/03/201630
12/03/201630

 

So, if I Sum the Dates I have this:

DATAVALUE
10/201620
11/201640
12/201660

 

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
DanJaques
Regular Visitor

Re: Sum by Month

I solve it

Measure = CALCULATE(SUM(Sheet1[Value]);FILTER(ALL(Sheet1);Sheet1[Value]<=MAX(Sheet1[Value])))

 

 

Capture.PNG

View solution in original post

6 REPLIES 6
rajendran Resident Rockstar
Resident Rockstar

Re: Sum by Month

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.

 

sumbymonth.PNG

 

Thanks
Raj

DanJaques
Regular Visitor

Re: Sum by Month

Hi @rajendran

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.

Capture.PNG

 

 

 

 

 

 

 

 

 

But if I change the date I lost my value who actually should be  310(full value January)+580(full value of january) = 890

Capture1.PNG

  

 

rajendran Resident Rockstar
Resident Rockstar

Re: Sum by Month

Do you still need date slicer, as you have date_Month slicer, which serves the same purpose?

 

Thanks

Raj

DanJaques
Regular Visitor

Re: Sum by Month

@rajendran 

Sadly, yes, because I already have a dinamic dashboard who the managers use the slicer to see weeks and fortnights about another graphics.

 Also, I have another dashboards who sincronize with this slicer.

 

As possible, this measure that I would like to make is for create a goal to another graphic mine

 

DanJaques
Regular Visitor

Re: Sum by Month

All the number of my days per month are equals, so i'm trying do something like this:

SUM IF (FITLER ALL sheet[data].[month] < MAX (sheet[data].[month])

DanJaques
Regular Visitor

Re: Sum by Month

I solve it

Measure = CALCULATE(SUM(Sheet1[Value]);FILTER(ALL(Sheet1);Sheet1[Value]<=MAX(Sheet1[Value])))

 

 

Capture.PNG

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors