Calculating the cost of the current month with the last month

[idriss]

Greetings Power Bi Community.

I'm new to Power Bi and i'm trying to calculate the Cumulative Planned Value from (EVM)

 I want to calculate the Cumulative Planed Value, and the formula i used is :

    CPV (for each column) = TBC(of the column ) + TBC(of the column before)

Any assistance would be appreciated.

[v-ljerr-msft]

Hi @idriss,

Without knowing your table structures, it's hard do write the formula which works exactly on your side. The formula below is for your reference. 

CPV =
VAR currentYear =
    MAX ( 'Table1'[Year] )
VAR currentMonth =
    MAX ( 'Table1'[Month] )
RETURN
    CALCULATE (
        SUM ( 'Table1'[TBC] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Year] = currentYear
                && 'Table1'[Month] <= currentMonth
        )
    )

Note: You'll need to replace the bold with your real table name and column name.

Regards

[idriss]

Hi @v-ljerr-msft

Thank you for the reply. I've tried your formula the result is just the total of everything.

I guess the problem is how to transform that table to relational table. 

If you have any clues, i'll be greatful.

[v-ljerr-msft]

Hi @idriss,

Could you post your real table structures(including the relationship) with some sample\mock data and the expected result, so that we can further assist on this issue? It's better to just share a sample pbix file(with some mock data). You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[idriss · Accepted solution]

Hi @v-ljerr-msft

Thank you so much for everything. after adjusting the formula you gave me. I was able to calculate the Cumulative Planed Value.

the correct furmula is below :

CPV =
CALCULATION (
          SUM( 'Table1'[TBC] ,
          FILTER (
                ALL ( 'Table1' ,
                        'Table1'[Month] <= EARLIER (  'Table1'[Month] )

                        )
    )

And again thank you.

good day.