Forum Discussion

jaco1951's avatar
jaco1951
Helper III
8 years ago
Solved

Accumulate in groups

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

Show and Tell
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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