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 ite...
  • 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)

     

     

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)