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.
The variables _currentMaxDate and _currentMinDate will return blank if there are no values for that week in the sales table. So they would then not be able to filter the virtual table as they are both blank.
You should be able to change the coding as follows...
var _currentMaxDate =
IF(
ISBLANK(MAX('salesTable'[Date])),
MAX(dateTable[date]),
MAX(salesTable[Date])
)
var _currentMinDate =
IF(
ISBLANK(MIN('salesTable'[Date])),
MIN(dateTable[date]),
MIN(salesTable[Date])
)
Hey, i stumpled upon another small issue.
When i have no sales in for example week 14, its showing me the total amount of the whole table on the first selected week. This first selected week is supposed to show everything accumulated + the first selected week.
This measure seem to work on week 1-13 even when there are no sales in those weeks. but after week 14 the empy weeks show me the total amount of the whole table. Not sure why this is.