Cumulative Values combined with non cumulative values in a column -i have a flag column-align

[sjoerdbuis]

Hi,

I have a numbers colum where cumulative values are combined with non cumulative values

The amounts are flagged. How do i create a numbers column where

If numbers value has flag A then cumulative into new numbers column

if numbers values has flag B the numbers stay the same 

In this case I want to have quarterly cumulated not QTD

[v-cherch-msft · Accepted solution]

Hi @sjoerdbuis 

You may create a calculated column like below:

Column =
IF (
    Table1[Flag] = "B",
    Table1[Value],
    CALCULATE (
        SUM ( Table1[Value] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Flag] = "A"
                && Table1[Date] <= EARLIER ( Table1[Date] )
        )
    )
)

Regards,

[sjoerdbuis]

Regarding:

Column =
IF (
    Table1[Flag] = "B",
    Table1[Value],
    CALCULATE (
        SUM ( Table1[Value] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Flag] = "A"
                && Table1[Date] <= EARLIER ( Table1[Date] )
        )
    )
)

If my Date column is in another table, say Table 2, how can i select this table in this function now I can only select Table1 after &&. So the filter needs to look at table1 and table 2