cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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)

 

 

Capture.PNG

 

 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

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.

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
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. Smiley Happy

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

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.

Highlighted
Microsoft
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 uploadingSmiley Happy

 

Regards

Highlighted
Regular Visitor

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

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.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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)