cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User I
Super User I

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
Super User I
Super User I

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

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!

@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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors