Running Total Per Month

[Aku_2800]

I want to create a running total per month based on sales amount of particular values.
Also, those particular values were not sold each month but still need running total of sales amount for all items for entire year.

Here's an example:-
Input Table                                                                Output Table

Item	Month	Sales		Item	Month	Sales	cumulative Total
Apple	Feb-24	236		Apple	Jan-24	0	0
Apple	Jun-24	112		Apple	Feb-24	236	236
Apple	Mar-24	404		Apple	Mar-24	404	640
Guava	Aug-24	442		Apple	Apr-24	0	640
Guava	Dec-24	226		Apple	May-24	0	640
Guava	Feb-24	274		Apple	Jun-24	112	752
Guava	Jan-24	285		Apple	Jul-24	0	752
Guava	Jun-24	146		Apple	Aug-24	0	752
Guava	Nov-24	423		Apple	Sep-24	0	752
Melon	Apr-24	438		Apple	Oct-24	0	752
Melon	Jul-24	391		Apple	Nov-24	0	752
Melon	Mar-24	188		Apple	Dec-24	0	752
Melon	Nov-24	221		Guava	Jan-24	285	285
Melon	Oct-24	477		Guava	Feb-24	274	559
				Guava	Mar-24	0	559
				Guava	Apr-24	0	559
				Guava	May-24	0	559
				Guava	Jun-24	146	705
				Guava	Jul-24	0	705
				Guava	Aug-24	442	1147
				Guava	Sep-24	0	1147
				Guava	Oct-24	0	1147
				Guava	Nov-24	423	1570
				Guava	Dec-24	226	1796
				Melon	Jan-24	0	0
				Melon	Feb-24	0	0
				Melon	Mar-24	188	188
				Melon	Apr-24	438	626
				Melon	May-24	0	626
				Melon	Jun-24	0	626
				Melon	Jul-24	391	1017
				Melon	Aug-24	0	1017
				Melon	Sep-24	0	1017
				Melon	Oct-24	477	1494
				Melon	Nov-24	221	1715
				Melon	Dec-24	0	1715

[talespin · Accepted solution]

hi @Aku_2800 

Here are your fruits 😄

Step1:

Create a Calendar table, add year, month and month no. as well.

CALENDAR =

VAR _MinYear = YEAR(MIN(Fruits[Month]))

VAR _MinDate  = DATE( _MinYear, 1, 1)

VAR _MaxYear = YEAR(MAX(Fruits[Month]))

VAR _MaxDate = DATE(_MaxYear, 12, 31)

RETURN CALENDAR(_MinDate, _MaxDate)

Step2: Create this measure

Fruit Sales =

VAR _Year = SELECTEDVALUE('CALENDAR'[YEAR])

VAR _Month = SELECTEDVALUE('CALENDAR'[MonthNo])

VAR _Dt = EOMONTH(DATE(_Year, _Month, 1), 0)

VAR _Sales = SUMX( FILTER( ALL('CALENDAR'[Date]), [Date] <= _Dt),

             CALCULATE( SUM(Fruits[Sales]) )

)

RETURN IF( ISBLANK(_Sales), 0, _Sales)