Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
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
vivran22
Community Champion
Community Champion

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
Community Champion
Community Champion

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

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
Community Champion
Community Champion

@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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.