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...
Need Help DAX
  • johnt75's avatar
    johnt75
    3 years ago

    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
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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
3 years ago

This works great - thank you!