Forum Discussion

Aku_2800's avatar
Aku_2800
Frequent Visitor
2 years ago
Solved

Running Total Per Month

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

ItemMonthSales ItemMonthSalescumulative Total
AppleFeb-24236 AppleJan-2400
AppleJun-24112 AppleFeb-24236236
AppleMar-24404 AppleMar-24404640
GuavaAug-24442 AppleApr-240640
GuavaDec-24226 AppleMay-240640
GuavaFeb-24274 AppleJun-24112752
GuavaJan-24285 AppleJul-240752
GuavaJun-24146 AppleAug-240752
GuavaNov-24423 AppleSep-240752
MelonApr-24438 AppleOct-240752
MelonJul-24391 AppleNov-240752
MelonMar-24188 AppleDec-240752
MelonNov-24221 GuavaJan-24285285
MelonOct-24477 GuavaFeb-24274559
    GuavaMar-240559
    GuavaApr-240559
    GuavaMay-240559
    GuavaJun-24146705
    GuavaJul-240705
    GuavaAug-244421147
    GuavaSep-2401147
    GuavaOct-2401147
    GuavaNov-244231570
    GuavaDec-242261796
    MelonJan-2400
    MelonFeb-2400
    MelonMar-24188188
    MelonApr-24438626
    MelonMay-240626
    MelonJun-240626
    MelonJul-243911017
    MelonAug-2401017
    MelonSep-2401017
    MelonOct-244771494
    MelonNov-242211715
    MelonDec-2401715
  • talespin's avatar
    talespin
    2 years ago

    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)

     

     

1 Reply

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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)