Accumulated value based on criteria

[Johan]

Hi,

I've got this measure to create:
show accumulated costs only in the month when the project is delivered

project table:
projectid deliverymonth
1 201702
2 201703

costs table
month projectid amount
201701 1 100
201702 1 200
201703 1 300
201701 2 111
201702 2 222
201703 2 333

result should show:
projectid month acc.costs at delivery
1 201701 0
1 201702 200
1 201703 0
2 201701 0
2 201702 0
2 201703 666

If tried sumx and if options, but no luck so far.

Thanks,
Johan.

[v-sihou-msft · Accepted solution]

@Johan

I assume your two tables linked by Project Id. Then you just need to add a calculated column like below:

Cumulative =
IF (
    Costs[Month] = RELATED ( Project[DeliveryMonth] ),
    CALCULATE (
        SUM ( Costs[Amount] ),
        FILTER (
            Costs,
            Costs[Month] <= EARLIER ( Costs[Month] )
                && Costs[Project] = EARLIER ( Costs[Project] )
        )
    ),
    0
)

Regards,

[Johan]

Thanks. 

through the msdn network I found a calculated measure :

if(maxx(month) = max(project[deliverymonth); [accumulated value];0) 

where [accumulated value] is the regular dax formula for accumulations.

I like your solution because for performance reasons it might be better to put into the table as new column. Though I think it should be a column in  the project table, since there are multiple cost lines, which would duplicate the value since in the month of delivery you can have multiple lines calculating the same column value.

Thanks for your input!