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

 

Need Help
  • jgeddes's avatar
    jgeddes
    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.

8 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    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.

    • Robin96's avatar
      Robin96
      Helper II
      2 years ago

      Hey, 

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

      Much appreciated 🙂

    • Robin96's avatar
      Robin96
      Helper II
      2 years ago

      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's avatar
        jgeddes
        Super User
        2 years ago

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