topic Re: Running total at the end off each month in DAX Commands and Tips

Running total at the end off each month

Anonymous — Fri, 07 May 2021 12:36:57 GMT

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)

Re: Running total at the end off each month

Jihwan_Kim — Fri, 07 May 2021 12:59:05 GMT

Hi, Anonymous

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

Re: Running total at the end off each month

Anonymous — Fri, 07 May 2021 13:00:54 GMT

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

Re: Running total at the end off each month

ERD — Fri, 07 May 2021 13:44:01 GMT

Hi Anonymous ,

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.