Accumulate everything before + the selected date

[Robin96]

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 week. is there an easy way in DAX to do this?

i have a date table with these columns:

Was thinking thay my Year_week_rolling_zero column would work for this, but i have trouble with my calculation...

This is one code i have tried before and it seems to work, but there are some weeks that give me values that should not be there..
i filter out my sales that are in a status called "1" and "9"

VAR EarliestSelectedDate = MIN('Dimension Date'[Year_week])

VAR customer_invoice = CALCULATE(SUM('Fact Sales'[Totals Converted gross Amount]),'Fact Sales'[Status] <> 9 && 'Fact Sales'[Status] <> 1)

VAR Before_Date =
    CALCULATE (
        SUM('Fact Sales'[Totals Converted Net Amount]),'Fact Sales'[Status] <> 9 && 'Fact Sales'[Status] <> 1, 
        FILTER (
            ALL('fact sales'),
            RELATED('Dimension Date'[Year_week]) <= EarliestSelectedDate
        )
    )

RETURN 
IF(MIN('Dimension Date'[Week Number])>CALCULATE(MIN('Dimension Date'[Week Number]), ALLSELECTED('Dimension Date'[Week Number])),customer_invoice,Before_Date)

[jgeddes · Accepted solution]

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.

[Robin96]

after some testing, i find that on some weeks where there are no sales, the accumulation before that date stops. i have images as reference:

Here it is working fine, its accumulating everything everything before week 9 and placing that on the week 9.

When i select period from week 14, the accumulation is blank. not sure why.

[jgeddes]

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])
)

[Robin96]

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.

[jgeddes]

This is curious. When you say week 14 does not have sales, are there still values in the salesTable for week 14 that are not 'sales'?

[Robin96]

Hey, There are no rows in the "Sales Table" for week 14 at all.

[Robin96]

Thank you, this worked aswell! 🙂

[Robin96]

Hey, 

Thank you so much for this! This worked exactly the way i wanted it to work!

Much appreciated 🙂