cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

New Table - Cumulative Rolling Total By Visible Columns

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_yearfin_month_noDIVISIONCUSTOMER_NUMBERFull Part DescriptionSales
20201001196Product 1500
20202001196Product 1500
20202001196Product 21000
20203001196Product 1500
20203001196Product 21000

 

1 ACCEPTED SOLUTION

Accepted Solutions
Helper III
Helper III

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

View solution in original post

2 REPLIES 2
Helper III
Helper III

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

View solution in original post

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


Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors