cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Cumulative Sum by month per Product

Hi PowerBI Community

I looking to calculate running total by Month, Product and category.

Basically i have rows of data - each row represents the volume sold to PRODUCT per day, please note the table don't have date only month, so i created a dummy date as 1/1/2020 for Jan, 1/2/2020 for Feb and 1/4/2020 for Apr likewise. Data model has relationship between Sales Table date and Date Table date.

I'm looking for Cumulative sum of volume by adding month over month per PRODUCT per category (drill down). My datasets and the output diagram as shown in the diagram. 1st table is datasets and the second one is the expected outcome.

SALES DATASETSOutputs - Cumulative Total month by month

I tried all different DAX combinations with

Calculate>>Filter>>ALL

Calculate>>Filter>>ALL Selected

i.e.

RT =
CALCULATE([Volumes],
FILTER(
ALL(DateTbl[Date]),
sales[Date] <= MAX(sales[Date])))

Also tried

RT =

CALCULATE([Volumes],
FILTER(
ALLSELECTED(DateTbl[Date]),
sales[Date] <= MAX(sales[Date])))

No success, any idea on how to complete this process or fixbale solution

Thank you so much for all your help!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous ,

Here we go:

``````Measure 2 =
VAR a =
MIN ( 'date'[Date] )
VAR maxd =
CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( 'Table1' ) )
RETURN
IF (
a <= maxd,
CALCULATE (
SUM ( Table1[Tonnes] ),
FILTER ( ALL ( 'date' ), 'date'[Date] <= a )
)
)``````

8 REPLIES 8
Super User

@Anonymous , Can you share sample data and sample output. in table format

Anonymous
Not applicable
 Month Sector Category Customer Tonnes Date Year April Continental Eurob 83 01/04/2020 2020 April Continental Eurob 90 01/04/2020 2020 April Distribution Gener 71 01/04/2020 2020 April Distribution Gener 15 01/04/2020 2020 April Distribution Gener 59 01/04/2020 2020 April Ethnic Conti 21 01/04/2020 2020 April Export IBCO 10 01/04/2020 2020 February Continental Eurob 10 01/02/2020 2020 February Continental Eurob 13 01/02/2020 2020 February Distribution Gener 36 01/02/2020 2020 February Distribution Gener 26 01/02/2020 2020 February Ethnic Conti 36 01/02/2020 2020 February Ethnic Conti 36 01/02/2020 2020 February Export IBCO 83 01/02/2020 2020 February Export IBCO 14 01/02/2020 2020 February Export IBCO 22 01/02/2020 2020 January Continental Eurob 45 01/01/2020 2020 January Continental Eurob 62 01/01/2020 2020 January Distribution Gener 97 01/01/2020 2020 January Ethnic Conti 65 01/01/2020 2020 January Ethnic Conti 56 01/01/2020 2020 January Export IBCO 81 01/01/2020 2020 January Export IBCO 41 01/01/2020 2020 January Export IBCO 14 01/01/2020 2020 March Continental Eurob 24 01/03/2020 2020 March Distribution Gener 73 01/03/2020 2020 March Distribution Gener 60 01/03/2020 2020 March Distribution Gener 68 01/03/2020 2020 March Export IBCO 12 01/03/2020 2020 March Export IBCO 41 01/03/2020 2020 March Export

 Sector Category Customer January February March April Continental Eurob 107 130 154 327 Distribution Gener 97 159 360 505 Ethnic Conti 121 193 193 214 Export IBCO 136 255 396 406

1st Table - Sales Table

2nd Table - Final Output

Datamodel - SalesTbl[Date] to DateTbl[Date] - Many To One.

Thanks@

Anonymous
Not applicable

Hi @Anonymous ,

Here we go:

``````Measure 2 =
VAR a =
MIN ( 'date'[Date] )
VAR maxd =
CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( 'Table1' ) )
RETURN
IF (
a <= maxd,
CALCULATE (
SUM ( Table1[Tonnes] ),
FILTER ( ALL ( 'date' ), 'date'[Date] <= a )
)
)``````

Regular Visitor

You're a life saver 😊

Anonymous
Not applicable

@Anonymous - You are awesome, suggested DAX worked magically.

Employee

I put your example data in and was able to get your desired output with a measure like this and a matrix visual with Sector Category and Customer on the rows and Month on the column.

Sales YTD = var maxdate = MAX('Date'[Date])
Return CALCULATE(SUM(Sales[Tonnes]), All('Date'), 'Date'[Date]<= maxdate)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Anonymous
Not applicable

@mahoneypat  @amitchandak
I would like to apply a filter every month like
Ie, For example if I select May month, It should display like this for me =

Cumulative April_FY=
Var April = CALCULATE(SUM(new_shptransactionscurrentyears[new_netbalance]), new_shptransactionscurrentyears[new_postingdate].[monthNo] = 4)
Var May = CALCULATE(SUM(new_shptransactionscurrentyears[new_netbalance]), new_shptransactionscurrentyears[new_postingdate].[monthNo] = 5)
return (April + May)

With the above Dax Table coming out as Cumulative net balance Expecting as cumulative April (Achieved using the mentioned DAX)

Help is greatly appreciated
Anonymous
Not applicable

Hello @mahoneypat
Thank you for the DAX, It worked great for the table however not working fine for the matrix table

Summing up the value in the matrix table

Matrix not working fine

Table - working fine

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors