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

Subtracting the value from previous value

In the Excel, I am using the formula =IF((D4-D5)=(C5-C4),"OK",((D4-D5)/(C5-C4))) to calculate the productivity for each week. However in Power BI I am unable to locate any such option to select individual cell. Can anyone please suggest how I can run this formula in BI please?

1st Coloumn= Date, 2nd - Orig Estimation, 3rd aka C - Completed Work, Coloumn 4 aka D is Remaining days

24 February 20201670.52017418.57652435.5
02 March 20201670.52122.75352.758052475.5
09 March 20201670.52222.25299.5851.252521.75
16 March 20201320.52082.25153914.752235.25

 

 

All I want a value subtraction from  299.5-153 and 914.25 -854.75 in a new coloumn. Any suggestion 

 

Also, is  there any way in which I can add information manually in the BI instead of adding CSV or excel file every week. My data only changes once in a week. However, I have to load entire CSV file every week to analysis.

 

Please suggest,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Subtracting the value from previous value

Hi @amitt0001 , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: Subtracting the value from previous value

Hi @amitt0001 , 

If you want to get result of current row-previous row, you could refer to my sample for details. If not , please correct me and inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: Subtracting the value from previous value

https://1drv.ms/x/s!ApDgFRXBWrzSp313Wm95IfWWiRqw?e=20MDyz 

 

Hi Zoe,

 

I have attached the excel sheet. Basically I am looking for the formula in Column G, H and I. Any help would be much appreciated. At the time being we can overlook formula in column F. 

 

The formula in column E named as 'Overspend' is easy. I reckon I would be able to do it. 

 

Thank you again,

 

Regards,

Amit

Highlighted
Community Support
Community Support

Re: Subtracting the value from previous value

Hi @amitt0001 , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Subtracting the value from previous value

Hi Zoe,

 

Can you please explain me the logic behind this formula

 

= Table.TransformColumnTypes(Source,{{"Date", type date}, {"Original Estimate", Int64.Type}, {"Completed Work", Int64.Type}, {"Remaining Work", Int64.Type}, {"Overspend", Int64.Type}, {"Productivity ", Percentage.Type}, {"Work done in Completed work each week", Int64.Type}, {"Work done in Remaining Work each wek", Int64.Type}, {"Productivity each week", Percentage.Type}})

 

Thank you 🙂

Highlighted
Community Support
Community Support

Re: Subtracting the value from previous value

Hi @amitt0001 , 

This is code to change data type to what you want, you could see it in preview(they show as int or percentage )

651.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: Subtracting the value from previous value

 Dear Zoe,

 

I agree with you. Those are integer. However, I need to konw the formula or logic behind how did you substracted value from data. 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!