cancel
Showing results 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.

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

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

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)

Solution Sage

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)

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors