cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Cumulative sum shows value beyond last month

Hi there,

 

I have created a measure that calculates the cumulative sum until last month. Here is my measure:

**bleep** sum =
var CurrentDate = MAX('Outages '[Actual Interruption Start Date Time])
Var lastmonth =EOMONTH(TODAY(),-1)
Var _CYMonth = MONTH(TODAY())
Var _RYMonth = if(_CYMonth > 3 && _CYMonth <= 12, _CYMonth - 3,_CYMonth + 9)

Return
sumx(FILTER(ALL('Outages'),'Outages '[Actual Interruption Start Date Time]<=CurrentDate && 'Outages '[Actual Interruption Start Date Time ] <=lastmonth ),
[sum_measure])
 
The above measure produced the following output
 
:Screenshot 2020-10-27 121153.jpg
 
 
The problem with the above output is that it shows the value of October and November. It should show value up to September.
The expected output would be like:
leo_89_0-1603759135808.png

 

I am not sure where am I making the mistake. Any help would be really appreciated.
 
 
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Hi,

Assuming you have dragged Month name from the Calendar Table, this should work

=if(max(calendar[date])>today(),blank(),[your measure])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Highlighted
Frequent Visitor

Check out this method:

https://www.sqlbi.com/blog/marco/2018/08/10/the-hidden-secrets-of-totalytd/

 

You should use the CALCULATE  and  DATESYTD approach.

Highlighted

@grandtotal  I am not interested in calculating DateYTD. I wanted to calculate the cumulative sum by the end of last month.

Highlighted
Super User III
Super User III

Hi,

Assuming you have dragged Month name from the Calendar Table, this should work

=if(max(calendar[date])>today(),blank(),[your measure])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors