New Table - Cumulative Rolling Total By Visible Columns

[Tuan]

I'm trying to create a "New Table" off of a existing dataset that does a Cumulative Rolling Total by Year, Month, Customer, Etc.

Current Code

Cumulative Sales = SUMMARIZE(VW_FIN_SALES_INVOICES
,VW_ROSSDW_DIM_DATE[fin_year]
,VW_ROSSDW_DIM_DATE[fin_month_no]
,CUSTOMER_ADDRESSES[DIVISION]
,CUSTOMER_ADDRESSES[CUSTOMER_NUMBER]
,VW_FIN_SALES_INVOICES[CF_SHIP_TO_ID]
,PRODUCT_MASTER[PRODUCT_GROUP]
,PRODUCT_MASTER[Product Groups]
,PRODUCT_MASTER[Product Class]
,PRODUCT_MASTER[Full Part Description]
,"Sales",CALCULATE(sum(VW_FIN_SALES_INVOICES[SOP_INVOICE_TOTAL_BASE]),
            ALL(VW_ROSSDW_DIM_DATE),VW_ROSSDW_DIM_DATE[fin_month_no]<=EARLIER(VW_ROSSDW_DIM_DATE[fin_month_no]))
)

 Example of end result

fin_year	fin_month_no	DIVISION	CUSTOMER_NUMBER	Full Part Description	Sales
2020	1	00	1196	Product 1	500
2020	2	00	1196	Product 1	500
2020	2	00	1196	Product 2	1000
2020	3	00	1196	Product 1	500
2020	3	00	1196	Product 2	1000

[Tuan · Accepted solution]

Got to the data i need, don't think it the best way to do it.

Summarized my Fact Data and use my Dim date table to repeat the Months and year.

Test = ADDCOLUMNS(SUMMARIZECOLUMNS(
VW_ROSSDW_DIM_DATE[fin_year]
,VW_ROSSDW_DIM_DATE[fin_month_no]
,VW_FIN_SALES_INVOICES[Division]
,VW_FIN_SALES_INVOICES[Customer,Number]
,VW_FIN_SALES_INVOICES[CF_SHIP_TO_ID]
,VW_FIN_SALES_INVOICES[Product,Group]
,VW_FIN_SALES_INVOICES[Product]
),"Sales",[Gross Sales]
)

 Then I did a rolling total

Cumulative Totals = 
CALCULATE (
    SUM (Test[Sales] ),
    FILTER (
        'test',
        'test'[Product] = EARLIER ( 'test'[Product] )
            && 'Test'[fin_year] = EARLIER ( 'Test'[fin_year] )
            && 'Test'[CF_SHIP_TO_ID] = EARLIER ( 'Test'[CF_SHIP_TO_ID] )
            && 'Test'[fin_month_no] <= EARLIER ( Test[fin_month_no] )
    )
)

[v-easonf-msft]

Hi , @Tuan 

Could you please tell me whether your problem has been solved?
If it is,  please mark your reply as Answered to close this thread.

Best Regards,
Community Support Team _ Eason