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
Highlighted
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
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Attending MBAS? Claim your badge

Attending MBAS? Claim your badge

Whether you’re streaming the digital event live, or watching on-demand, claim your attendee badge to sport on your profile.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors