Forum Discussion
Accumulate everything before + the selected date
- 2 years ago
One way to accomplish this is by building a virtual table in a measure and then taking the sums from that table.
Assuming you have a model where a date table that contains week definitions is related to a sales table by day then a sample measure could look something like...Measure = var _earliestWeek = MINX( ALLSELECTED('dateTable'), 'dateTable'[Week] ) var _currentWeek = SELECTEDVALUE('dateTable'[Week]) var _currentMaxDate = MAX('salesTable'[Date]) var _currentMinDate = MIN('salesTable'[Date]) var _vTable = SUMMARIZE( FILTER(All('salesTable'), 'salesTable'[Status] <> 9 && 'salesTable'[Status] <> 1), 'salesTable'[Date], "_value", IF( _currentWeek = _earliestWeek, SUMX( FILTER('salesTable', 'salesTable'[Date] <= _currentMaxDate), 'salesTable'[Sales] ), SUMX( FILTER('salesTable', 'salesTable'[Date] <= _currentMaxDate && 'salesTable'[Date] >= _currentMinDate), 'salesTable'[Sales] ) ) ) var _result = SUMX( _vTable, [_value] ) Return _resultThis measures use the 'SUMMARIZE' function to construct a vitual table that contains all of the rows from the salesTable where the status is not 9 or the status is not 1. From there it creates a '_value" column that is populated with Sales values that are determined by whether the current row in the virtual table is the earliest week row or not. This approach relies on the context of the week row to be supplied from the visual. (i.e., a table or matrix etc.)
Here is a quick snapshot of a sample I created.Hopefully this gets you pointed in the right direction.