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 not,please share the sample data. If it resolved , please share the solution here. 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.

[v-menakakota]

Hi @jabrillo ,

Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you,
Menaka 

[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 · Accepted solution]

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