Forum Discussion
AUaero
Responsive Resident
3 years agoRunning 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
_CustomersLYDataHere'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
- johnt75
Super User
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
Responsive Resident
This works great - thank you!