cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Responsive Resident

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!

1 ACCEPTED SOLUTION
Super User

Try

``````Date exceeded last year =
VAR LastYear = YEAR( TODAY( ) ) - 1
VAR SalesLastYear =
CALCULATE( [# Billed Tons], 'Date Dim'[YEAR] = LastYear )
VAR SummaryTable =
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 2
Super User

Try

``````Date exceeded last year =
VAR LastYear = YEAR( TODAY( ) ) - 1
VAR SalesLastYear =
CALCULATE( [# Billed Tons], 'Date Dim'[YEAR] = LastYear )
VAR SummaryTable =
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``````
Responsive Resident

This works great - thank you!

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors