cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors