Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Aku_2800
New Member

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
1 ACCEPTED SOLUTION
talespin
Solution Sage
Solution Sage

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_0-1708164306373.png

 

View solution in original post

1 REPLY 1
talespin
Solution Sage
Solution Sage

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_0-1708164306373.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors