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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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 DATASETSSALES DATASETSOutputs - Cumulative Total month by monthOutputs - 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 )
        )
    )

44.PNG

 

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

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

Anonymous
Not applicable

Month

Sector CategoryCustomerTonnesDateYear
AprilContinentalEurob8301/04/20202020
AprilContinentalEurob9001/04/20202020
AprilDistributionGener7101/04/20202020
AprilDistributionGener1501/04/20202020
AprilDistributionGener5901/04/20202020
AprilEthnicConti2101/04/20202020
AprilExportIBCO 1001/04/20202020
FebruaryContinentalEurob1001/02/20202020
FebruaryContinentalEurob1301/02/20202020
FebruaryDistributionGener3601/02/20202020
FebruaryDistributionGener2601/02/20202020
FebruaryEthnicConti3601/02/20202020
FebruaryEthnicConti3601/02/20202020
FebruaryExportIBCO 8301/02/20202020
FebruaryExportIBCO 1401/02/20202020
FebruaryExportIBCO 2201/02/20202020
JanuaryContinentalEurob4501/01/20202020
JanuaryContinentalEurob6201/01/20202020
JanuaryDistributionGener9701/01/20202020
JanuaryEthnicConti6501/01/20202020
JanuaryEthnicConti5601/01/20202020
JanuaryExportIBCO 8101/01/20202020
JanuaryExportIBCO 4101/01/20202020
JanuaryExportIBCO 1401/01/20202020
MarchContinentalEurob2401/03/20202020
MarchDistributionGener7301/03/20202020
MarchDistributionGener6001/03/20202020
MarchDistributionGener6801/03/20202020
MarchExportIBCO 1201/03/20202020
MarchExportIBCO 4101/03/20202020
MarchExport    

 

Sector CategoryCustomerJanuaryFebruaryMarchApril
Continental     
 Eurob107130154327
Distribution     
 Gener97159360505
Ethnic     
 Conti121193193214
Export     
 IBCO 136255396406

 

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

44.PNG

 

You're a life saver 😊

 

Anonymous
Not applicable

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

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

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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)
BS097_0-1660060962613.png

 



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



 BS097_0-1660058130059.png
Matrix not working fine

Table - working fine

BS097_1-1660058168470.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors