Running sum per month

[JGRaiol]

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"):

Date	Value
06/03/2020	1819
05/03/2020	1819
04/03/2020	777
03/03/2020	797
02/03/2020	266
29/02/2020	132
29/02/2020	132
28/02/2020	71
27/02/2020	70
26/02/2020	71
25/02/2020	74
24/02/2020	73
23/02/2020	76
22/02/2020	124

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

Date	Value	Running sum
06/03/2020	1819	5477
05/03/2020	1819	3659
04/03/2020	777	1840
03/03/2020	797	1063
02/03/2020	266	266
29/02/2020	132	823
29/02/2020	132	691
28/02/2020	71	559
27/02/2020	70	487
26/02/2020	71	417
25/02/2020	74	347
24/02/2020	73	273
23/02/2020	76	200
22/02/2020	124	124

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:

Date	Value	Running sum
06/03/2020	1819	6300
05/03/2020	1819	4481
04/03/2020	777	2663
03/03/2020	797	1886
02/03/2020	266	1089
29/02/2020	132	823
29/02/2020	132	691
28/02/2020	71	559
27/02/2020	70	487
26/02/2020	71	417
25/02/2020	74	347
24/02/2020	73	273
23/02/2020	76	200
22/02/2020	124	124

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.

[vivran22 · Accepted solution]

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

[JGRaiol]

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]

@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