cumsum of weekly dax measure

[Anonymous]

hey team,
i hope you are doing well!
i've created a dax measure to get the last 12 weeks sales based on a slicer selection.
below is the weekly sales measure : 

Sellout_weekly = 
VAR _selyear = SELECTEDVALUE(weeksTable[year])
VAR _weekNum = SELECTEDVALUE(weeksTable[Week])
VAR _previousYear = _selyear - 1
VAR _weeksToGoBack = 12

-- Calculate the maximum week of the previous year (assuming it might be 52 or 53 weeks)
VAR _maxWeekPreviousYear = MAXX(FILTER(ALL(weeksTable), weeksTable[year] = _previousYear), weeksTable[Week])

-- Check if we need to go back to the previous year
VAR _isCrossYear = _weekNum <= _weeksToGoBack

-- Calculate the week range for both current year and previous year
VAR _StartWeekCurrentYear = IF(_isCrossYear, 1, _weekNum - _weeksToGoBack + 1)
VAR _StartWeekPreviousYear = _maxWeekPreviousYear - (_weeksToGoBack - _weekNum)

RETURN
CALCULATE(
    [Sellout],
    FILTER(
        SALES_SUMMARY_VIEW,
        (
            -- Handle the weeks from the current year
            (SALES_SUMMARY_VIEW[Week] <= _weekNum && SALES_SUMMARY_VIEW[Week] >= _StartWeekCurrentYear && SALES_SUMMARY_VIEW[Year] = _selyear)
        ) ||
        (
            -- Handle the weeks from the previous year if we need to go back
            _isCrossYear && SALES_SUMMARY_VIEW[Week] >= _StartWeekPreviousYear && SALES_SUMMARY_VIEW[Week] <= _maxWeekPreviousYear && SALES_SUMMARY_VIEW[Year] = _previousYear
        )
    )
)

 I have created another measure called weekly ecoulement which is : WeeklySellout - WeeklySellin

ps. weekly sellin is the same measure above but with sum(QTY_sellin)

now i want to get the cummulative ecoulement. (cumsum of ecoulement)

[Anonymous · Accepted solution]

Hi @Anonymous ,

Do you want to get 31,53,66 in cummulative ecoulement column?

I recommend that you get the previous time range based on the current time range, the current range of weeks should have a rule to follow, or are they values for a column in your table? I recommend that you provide your pbix file (does not contain sensitive data), it will help us understand the problem better.

You can also refer to this method first, assuming that each time range has a corresponding sequence number, and the sequence numbers are separated by 1.

columnsum = 
VAR current_week_range = MAX('Table'[WeekRange])
VAR last_week_range = current_week_range-1
VAR last_weekly_ecoulement = IF(ISEMPTY(FILTER(ALL('Table (2)'),'Table[WeekRange]=last_week_Range)),0,CALCULATE([weekly_ecoulement],'Table'[WeekRange]=last_week_range))
RETURN last_weekly_ecoulement +[weekly_ecoulement]

Best regards,

Mengmeng Li

[Anonymous]

not working 

[Bibiano_Geraldo]

Hi, please try the following DAX

CumulativeEcoulement = 
VAR _selyear = SELECTEDVALUE(weeksTable[year])
VAR _weekNum = SELECTEDVALUE(weeksTable[Week])

RETURN
CALCULATE(
    [WeeklyEcoulement],
    FILTER(
        ALL(weeksTable),
        -- Accumulate from the start of the selected year and week
        (
            weeksTable[year] = _selyear && weeksTable[Week] <= _weekNum
        ) ||
        -- Accumulate from previous years if needed
        (weeksTable[year] < _selyear)
    )
)