cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Johan Regular Visitor
Regular Visitor

Accumulated value based on criteria

 

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.

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Accumulated value based on criteria

@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
)

7.PNG

 

 

Regards,

2 REPLIES 2
Moderator v-sihou-msft
Moderator

Re: Accumulated value based on criteria

@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
)

7.PNG

 

 

Regards,

Johan Regular Visitor
Regular Visitor

Re: Accumulated value based on criteria

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!