Forum Discussion

AUaero's avatar
AUaero
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Running Sum in a calculated table

Hi all,

I'm having trouble getting this one nailed down.  I'm trying to create a measure that will identify the date which a customers current year volume exceeds their total volume for the previous year.  To illustrate, I've cretaed a PBIX (attached) that summarizes the data into a calculated table.  The table returns a row for each date in the current year with a constant value of the previous year volume in a column (LYTons).  The Tons column should be a running sum for the current year, but I can't get my DAX to to return anything other than the volume on that given date.

Here's the code:

Running Sum Table = 
VAR _MaxInvoiceDate = 
CALCULATE(
    MAX(Sales[InvoiceDate]),
    ALL(Sales)
)

VAR _CurrentYear = 
YEAR(_MaxInvoiceDate)

VAR _LastYear = 
_CurrentYear - 1

VAR _CustomersLY = 
CALCULATETABLE(
    VALUES(Sales[CustomerAccountNumber]),
    ALL('Date Dim'),
    YEAR(Sales[InvoiceDate]) = _LastYear
)

VAR _DatesTY = 
CALCULATETABLE(
	VALUES('Date Dim'[DATE]),
	YEAR('Date Dim'[DATE]) = _CurrentYear
)

VAR _CustomersLYData = 
CALCULATETABLE(
	SUMMARIZE(
	    Sales,
	    Sales[CustomerAccountNumber],
	    'Date Dim'[DATE],
	    "LYTons", CALCULATE(
	        Sales[# Billed Tons], 
	        ALLEXCEPT(Sales, Sales[CustomerAccountNumber]),
	        YEAR(Sales[InvoiceDate]) = _LastYear
	    ),
	    "Tons", CALCULATE(
	    	Sales[# Billed Tons],
			ALL('Date Dim'),
			'Date Dim'[DATE] <= _MaxInvoiceDate
	    )
	),
	_CustomersLY,
	_DatesTY
)

RETURN
_CustomersLYData

Here's the link to a sample file:
https://1drv.ms/u/s!AtHPCBKFQogGgstDRaIUcA7A90yJSA?e=zd6gls

 

Thanks!

  • Try

    Date exceeded last year = 
    VAR LastYear = YEAR( TODAY( ) ) - 1
    VAR SalesLastYear =
    	CALCULATE( [# Billed Tons], 'Date Dim'[YEAR] = LastYear )
    VAR SummaryTable =
    	ADDCOLUMNS(
    		DATESYTD( 'Date Dim'[DATE] ),
    		"Running total",
    			CALCULATE( [# Billed Tons], DATESYTD( 'Date Dim'[DATE] ) )
    	)
    VAR Result =
    	MINX(
    		FILTER( SummaryTable, [Running total] > SalesLastYear ),
    		'Date Dim'[DATE]
    	)
    RETURN
    	Result

2 Replies

  • Try

    Date exceeded last year = 
    VAR LastYear = YEAR( TODAY( ) ) - 1
    VAR SalesLastYear =
    	CALCULATE( [# Billed Tons], 'Date Dim'[YEAR] = LastYear )
    VAR SummaryTable =
    	ADDCOLUMNS(
    		DATESYTD( 'Date Dim'[DATE] ),
    		"Running total",
    			CALCULATE( [# Billed Tons], DATESYTD( 'Date Dim'[DATE] ) )
    	)
    VAR Result =
    	MINX(
    		FILTER( SummaryTable, [Running total] > SalesLastYear ),
    		'Date Dim'[DATE]
    	)
    RETURN
    	Result
    • AUaero's avatar
      AUaero
      Icon for Responsive Resident rankResponsive Resident

      This works great - thank you!