Running total at the end off each month

[Hydrarian]

Hi,

I would like to create 12 measures for the 12 months of this year in which I calculate a running total up to each end of the months.

For now I'm using this type of formula, but I would like to make it more general (not only for this year, but also for years to come):

EXAMPLE FOR JANUARY:

SP_Net amount prog Mar = 
CALCULATE([SP_Net amount],
        'D - Date'[Date] >= DATE ( 2021, 1, 1 ) &&
        'D - Date'[Date] <= DATE ( 2021, 3, 31 ))

So I ask you if there is a function in order to calculate the last date of each months giving me examples (I know there is ENDOFMONTH but I don't understand if it is right in this case and how to use in this case (please, use my example)

[ERD]

Hi @Hydrarian ,

I'm not sure I've understood correctly what you need, but have a look at this measure:

#amt = 
var monthEnd = ENDOFMONTH('Date'[Date])
var currentYear = SELECTEDVALUE('Date'[Year])
var result = CALCULATE(
    [#fruits],
    'Date'[Date] < monthEnd,
    'Date'[Year] = currentYear
)
return
result

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Hydrarian 

I am not sure I understood your question correctly, but please try using DATESMTD or TOTALMTD function.

Something like below.

SP_Net amount prog Mar =
TOTALMTD ( 'D - Date'[Date], [SP_Net amount] )

If you can share your sample pbix file's link, then I can try to understand why you needed to write your measure like above, then I can try to come up with a more accurate measure.

Thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Hydrarian]

I give an example:

On january I have 2 apples, on february 1 apple, on march 8 apple. So:

JANUARY FORMULA RESULT=2

FEBRUARY FORMULA RESULT= 1 + 2 =3

MARCH FORMULA RESULT= 1 + 2 + 8 = 11