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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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