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

## Calculating the cost of the current month with the last month

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

## Re: Calculating the cost of the current month with the last month

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.

4 REPLIES 4
Highlighted
Microsoft

## Re: Calculating the cost of the current month with the last month

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

Highlighted
Regular Visitor

## Re: Calculating the cost of the current month with the last month

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.

Highlighted
Microsoft

## Re: Calculating the cost of the current month with the last month

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

Highlighted
Regular Visitor

## Re: Calculating the cost of the current month with the last month

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.

## Helpful resources

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (943)