Monthly Burn Rate

[jabrillo]

I want to create a line chart of monthly burn rate. This is how my file looks like:

Period	Actual Cost
1	100
2	120
3	140
4	100

This is what I want to accomplish:

Period	Burn Rate	Calculation
1	100	100/1
2	110	220/2
3	120	360/3
4	115	460/4

I tried this dax formula:

Burn Rate =

   VAR _PeriodCount = selectedvalue([Period])

   VAR _ActualCost = calculate(sum([Actual Cost]), [Period] <= max([Period]))

   VAR _BurnRate = divide(_ActualCost, _PeriodCount)

   RETURN _BurnRate

The burn rates for periods 2 to 4 were calculated incorrectly. Instead of dividing the YTD cost by the number of periods, it was dividing the cost of that period by the number of periods. What's wrong?

[v-menakakota]

Hi @jabrillo ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @jabrillo ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[lbendlin]

Burn Rate = 
var p = [Period]
return DIVIDE(SUMX(FILTER('Table',[Period]<=p),[Actual Cost]),p)

[jabrillo]

If Burn Rate is a measure, var p = [Period[ is not valid.

[lbendlin]

why should it be a measure?  Can it be impacted by user filters?

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Greg_Deckler]

@jabrillo Try:

Burn Rate =
   VAR _Period = MAX( 'Table'[Period] )
   VAR _Table = FILTER( ALL( 'Table' ), [Period] <= _Period )
   VAR _ActualCost = SUMX( _Table, [Actual Cost] )
   VAR _BurnRate = DIVIDE( _ActualCost, _Period )
RETURN 
   _BurnRate

[jabrillo]

The table would also have Project ID as another column. So the table looks like this:

Project ID	Period	Actual Cost