Forum Discussion
arpost
Post Prodigy
Post ProdigyHelp! How do I summarize and get a running total using DAX?
Greetings, PBI community! Have an interesting conundrum and wanted to see if anyone could offer a solution. Here's the two parts of the scenario with an example. Let's say I have a dataset set up...
Hi,
thank you for your feedback, and please check the attached pbix file if it suits your requirement.
Thank you.
Jihwan_Kim
Super User
Super UserHi,
Please check the below picture and the attached pbix file.
Most recent date: =
IF ( HASONEVALUE ( Customer[Customer] ), MAX ( Data[Date] ) )
Most recent call #: =
VAR _mostrecentdate = [Most recent date:]
RETURN
CALCULATE ( SUM ( Data[Calls] ), 'Calendar'[Date] = _mostrecentdate )
Calls accumulate by most recent calls: =
VAR _currentdate =
MAX ( 'Calendar'[Date] )
VAR _currentdatecallnumbers =
ADDCOLUMNS (
DISTINCT ( Customer[Customer] ),
"@lastdate", CALCULATE ( MAX ( Data[Date] ), 'Calendar'[Date] <= _currentdate )
)
RETURN
CALCULATE (
SUM ( Data[Calls] ),
TREATAS ( _currentdatecallnumbers, Customer[Customer], 'Calendar'[Date] )
)
arpostGreetings, Jihwan_Kim! Thanks for the reply. That's really helpful.
Quick question: this requires that there is a separate dimension table (Customers). Any chance you know of a solution that can work on the data in the data table itself? To put it another way, is there a form of the above that would work with just a Calendar table and a Data table?
- Jihwan_Kim
Hi,
thank you for your feedback, and please check the attached pbix file if it suits your requirement.
Thank you.