Need help with dynamic calculation based off of timeline

[smallon]

Hello,

I am a noobie here but trying to determine if this can be done. I am trying to calculate the total testing cost by multipling the test cost by the units sold. The issue I am running into is the cost of a test changes month to month. So when I put my calculation into the measure it sums the cost of the timeframe selected rather then calculating each month. How can this be done?

Example:

Jan-19 Test Cost is $18 Volume is 10

Feb-19 Test Cost is $19 Volume is 10

Mar-19 Test Cost is $25 Volume is 10

What I am getting:

Jan Cost: $180 (this is correct)

Jan-Feb Cost: $740 ($37 test cost x 20 volume)

Jan-Mar Cost: $1,860 ($62 test cost x 30 volume)

What I want to get:

Jan Cost: $180 (this is correct)

Jan-Feb Cost: $370 ($18 test cost x 10 volume, $19 test cost x 10 volume)

Jan-Mar Cost: $620 ($18 test cost x 10 volume, $19 test cost x 10 volume, $25 test cost x 10 volume)

Calculation: 

Total Cost = SUM('StarLIMS CPT'[Test Cost])*SUM('Total Rev'[Revenue Acode Volume])

I appreciate all the help!

[v-cherch-msft · Accepted solution]

Hi @smallon 

You may try to use below measure:

Total cost =
SUMX (
    FILTER (
        ALL ( 'StarLIMS CPT' ),
        'StarLIMS CPT'[Month] <= MAX ( 'StarLIMS CPT'[Month] )
    ),
    'StarLIMS CPT'[Test Cost] * 'StarLIMS CPT'[Revenue Acode Volume]
)

Regards,