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 incomin...
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

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

jaco1951's avatar
jaco1951
Helper III
8 years ago

Thank you Yuliana!