Forum Discussion

Robin96's avatar
Robin96
Helper II
2 years ago
Solved

Accumulate everything before + the selected date

Hey guys, i want to accumulate everything before my selected date + the values on the first selected date from my sales table. After the first selected week i want to show the normal values for that ...
  • jgeddes's avatar
    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
    _result

    This 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.