Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
dax
Community Support
Community Support

Hi @Anonymous , 

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
dax
Community Support
Community Support

Hi @Anonymous , 

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.

Anonymous
Not applicable

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

dax
Community Support
Community Support

Hi @Anonymous , 

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.

Anonymous
Not applicable

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 🙂

dax
Community Support
Community Support

Hi @Anonymous , 

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.

Anonymous
Not applicable

 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors