Accumulate in groups

[jaco1951]

Hi all

I have a large table with a key that is made of YearWeek, and a forecast usually four weeks forward. 

In the first row I have a startbalance, and in the forecasted weeks I have incoming and outgoing values that I need to add to the startbalance. I do also have a column with an Index.

(amountFreeCash +  amountRestrictedCash) + incoming - outgoing

How can I approac this best? By DAX or in Power Query?

Thanks in advance.

Br Espe

[v-yulgu-msft · Accepted solution]

Hi @jaco1951,

Please refer to below DAX formula:

StartBalance each week =
VAR Accumulateincomingprevious =
    CALCULATE (
        SUM ( 'YearWeek Forecast'[incoming] ),
        FILTER (
            'YearWeek Forecast',
            'YearWeek Forecast'[Index]
                <= EARLIER ( 'YearWeek Forecast'[Index] ) - 1
        )
    )
VAR Accumulateoutgoingprevious =
    CALCULATE (
        SUM ( 'YearWeek Forecast'[outgoing] ),
        FILTER (
            'YearWeek Forecast',
            'YearWeek Forecast'[Index]
                <= EARLIER ( 'YearWeek Forecast'[Index] ) - 1
        )
    )
VAR AccumulateamountFCprevious =
    CALCULATE (
        SUM ( 'YearWeek Forecast'[amountFC] ),
        FILTER (
            'YearWeek Forecast',
            'YearWeek Forecast'[Index]
                <= EARLIER ( 'YearWeek Forecast'[Index] ) - 1
        )
    )
VAR AccumulateamountRCprevious =
    CALCULATE (
        SUM ( 'YearWeek Forecast'[amountRC] ),
        FILTER (
            'YearWeek Forecast',
            'YearWeek Forecast'[Index]
                <= EARLIER ( 'YearWeek Forecast'[Index] ) - 1
        )
    )
RETURN
    IF (
        Accumulateincomingprevious = BLANK (),
        'YearWeek Forecast'[amountFC] + 'YearWeek Forecast'[amountRC],
        AccumulateamountFCprevious + AccumulateamountRCprevious
            + Accumulateincomingprevious
            - Accumulateoutgoingprevious
    )

EndBalance each week =
'YearWeek Forecast'[StartBalance each week] + 'YearWeek Forecast'[incoming]
    - 'YearWeek Forecast'[outgoing]

Best regards,

Yuliana Gu

[jaco1951]

Thank you Yuliana!