Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

Need Help
Tips and Tricks

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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!