Forum Discussion
Getting incremental values from cumulative figures
Hi,
I have a data set that records expected final fee and percentage complete for a number of projects, the table records one line per project per month.
Both the final fee and the percentage can go up or down as estimates are revised.
Each project has it's own unique project number.
A project will usually (but not always) have an entry for every month the project is live, even if there was no change on the project in that month.
I need to extract the incremental value change for each month, which will effectivly be:
(fee x percentage) - (fee [of previous entry for this project] x percentage [of previous entry for this project] )
It would be best if I could bring that through as part of the power query to add an additional column with the incremental change value, if that is not possible, the second best solution would be to add a column in DAX with the 3rd best solution doing as a measure.
Any help would be greatly appriciated.
Many thanks.
2 Replies
- Vijay_A_VermaMost Valuable Professional
Please post sample data.
How to get your questions answered quickly -- How to provide sample data
- BandersnatchrixNew Member
ProjectID Month Closed Fee Percentage Complete Cumulative Fee 553668 01/09/2018 0 100 0 553668 01/10/2018 0 100 0 553668 01/11/2018 0 100 0 553668 01/12/2018 0 100 0 553668 01/01/2019 182.5 100 182.5 553668 01/02/2019 182.5 100 182.5 553668 01/03/2019 182.5 100 182.5 553668 01/04/2019 182.5 100 182.5 553681 01/01/2019 21150 0 0 553681 01/02/2019 21150 0 0 553681 01/03/2019 21150 0 0 553681 01/04/2019 21150 0 0 553681 01/05/2019 21150 0 0 553681 01/06/2019 21150 0 0 553681 01/07/2019 21150 0 0 553681 01/08/2019 21150 0 0 553681 01/09/2019 21150 0 0 553681 01/10/2019 21150 46.42 9817.83 553681 01/11/2019 24900 39.43 9818.07 553681 01/01/2020 24900 39.43 9818.07 553681 01/02/2020 24900 39.43 9818.07 553681 01/03/2020 24900 100 24900 553681 01/04/2020 24900 100 24900 553683 01/11/2018 0 100 0 553683 01/12/2018 0 100 0 553683 01/01/2019 162000 0 0 553683 01/02/2019 162000 0 0 553683 01/03/2019 162000 0 0 553683 01/04/2019 162000 0 0 553683 01/05/2019 135480 0 0 553683 01/06/2019 135480 0 0 553683 01/07/2019 135480 0 0 553683 01/08/2019 135000 0.11 148.5 553683 01/09/2019 135000 0.11 148.5 553683 01/10/2019 135000 0.11 148.5 553683 01/11/2019 135000 0.11 148.5 553683 01/01/2020 135000 0.11 148.5 553683 01/02/2020 135000 0.11 148.5 553683 01/03/2020 135000 0.11 148.5 553683 01/04/2020 135000 0.11 148.5 553683 01/05/2020 135000 48.27 65164.5 553683 01/06/2020 135000 48.27 65164.5 553683 01/07/2020 135000 52.99 71536.5 553683 01/08/2020 135000 52.99 71536.5 553683 01/09/2020 135000 52.99 71536.5 553683 01/10/2020 135000 52.99 71536.5 553683 01/11/2020 135000 52.99 71536.5 553683 01/12/2020 135000 52.99 71536.5 553683 01/01/2021 65911.5 100 65911.5