cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JGRaiol Helper I
Helper I

Running sum per month

Greetings everyone!

 

I'm fairly new to PowerBI development and currently I am having some issues regarding the implementation of a Running Total per Month. I've searched a lot before writing this post, and I could not find any solution to my problem.

 

I have a table in the following format (consider the dates in the format "dd/mm/yyyy"):

 

DateValue
06/03/20201819
05/03/20201819
04/03/2020777
03/03/2020797
02/03/2020266
29/02/2020132
29/02/2020132
28/02/202071
27/02/202070
26/02/202071
25/02/202074
24/02/202073
23/02/202076
22/02/2020124

 

I need to implement a running sum per month, so the result should be something like:

 

DateValueRunning sum
06/03/202018195477
05/03/202018193659
04/03/20207771840
03/03/20207971063
02/03/2020266266
29/02/2020132823
29/02/2020132691
28/02/202071559
27/02/202070487
26/02/202071417
25/02/202074347
24/02/202073273
23/02/202076200
22/02/2020124124

 

The solutions I've found are pretty much the following DAX code:

 

 

CALCULATE (
    SUMX(FILTER('Table';'Table'[Date] >= STARTOFMONTH('Table'[Date])); 'Table'[Value]); 
    ALL ('Table');
    'Table'[Date]  <= EARLIER ( 'Table'[Date] )
)

 

 

 But that returns the running sum of the whole set of data, not respecting the "per month" criterion; like:

 

DateValueRunning sum
06/03/202018196300
05/03/202018194481
04/03/20207772663
03/03/20207971886
02/03/20202661089
29/02/2020132823
29/02/2020132691
28/02/202071559
27/02/202070487
26/02/202071417
25/02/202074347
24/02/202073273
23/02/202076200
22/02/2020124124

 

Which is not acceptable regarding my application.

 

Can you guys help me with this issue?

 

Thanks in advance!

 

Best regards,

 

J Raiol

 

@amitchandak I think this will be easy for you.

1 ACCEPTED SOLUTION

Accepted Solutions
vivran22 Memorable Member
Memorable Member

Re: Running sum per month

Hello @JGRaiol ,

 

You may try this:

 

MTD SUM = 
CALCULATE(
SUM([VALUE]),
DATESMTD([date])
)

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

View solution in original post

3 REPLIES 3
vivran22 Memorable Member
Memorable Member

Re: Running sum per month

Hello @JGRaiol ,

 

You may try this:

 

MTD SUM = 
CALCULATE(
SUM([VALUE]),
DATESMTD([date])
)

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

View solution in original post

JGRaiol Helper I
Helper I

Re: Running sum per month

Hi @vivran22, thanks for your help!

That works implemented as a measure!

 

One more quick question: can you give me any hints on how can I create a measure that could capture the dates in the current month? E.g. 01/april - Today, dinamically in the report, but it should not be varied with the Segment Data.

 

Thanks a lot!

vivran22 Memorable Member
Memorable Member

Re: Running sum per month

@JGRaiol 

 

I did not understand your second requirement. Can you demonstarte it with an example, the way you did earlier?

 

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

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.

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