Cumulative Sum by month per Product

[Anonymous]

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!

[amitchandak]

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

[Anonymous]

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@amitchandak

[Frank_Fei · Accepted solution]

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

[MichelleOmondi]

You're a life saver 😊

[Anonymous]

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

@amitchandak , @mahoneypat - Thanks for your suggestion and timely advice

[mahoneypat]

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

[BS097]

@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 

[BS097]

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 
Can you please help me

 
Matrix not working fine

Table - working fine