Forum Discussion
Helper IVSubtracting two measures in a matrix table and getting the wrong answers
Hi Guys,
When I try this measure below, my results are nowhere near the number I'm looking for. I know it's going to be hard to solve this one with the details given., The tables a matrix and I also have a master calendar
linked to my main fact table.
Date as Columns
Measures as values in the Matrix tabe.
Subtract = [Total to Earn - % Complete of Project] - [Measure Previous month]
Measure Previous month = CALCULATE(SUM(Merge1[Cumulative Revenue]),PARALLELPERIOD(Merge1[Date],-1,MONTH))
Total to Earn - % Complete of Project = [Sum of Current Budget] * [% Complete (Total Project)]
% Complete (Total Project) = CALCULATE(sum(Merge1[Cumulative Days])) / CALCULATE(sum(Merge1[Forecast Days]))
Sum of Current Budget = CALCULATE(SUM(Merge1[Current Budget (£k)]))
% Complete (Total Project) = CALCULATE(sum(Merge1[Cumulative Days])) / CALCULATE(sum(Merge1[Forecast Days]))
Here's the table.
| Project ID | Date | Days | Current Budget (£k) | Forecast Days | Cumulative Days | Previous Revenue Submitted (£) | Cumulative Revenue |
| AAA | 01/12/2018 | 0 | 10000 | 1000 | 0 | 0 | 0 |
| AAA | 01/06/2018 | 5 | 10000 | 1000 | 5 | 100 | 100 |
| AAA | 01/11/2018 | 10 | 10000 | 1000 | 15 | 200 | 300 |
| AAA | 01/10/2018 | 20 | 10000 | 1000 | 35 | 300 | 600 |
| AAA | 01/09/2018 | 25 | 10000 | 1000 | 60 | 400 | 1000 |
| AAA | 01/08/2018 | 31.5 | 10000 | 1000 | 91.5 | 500 | 1500 |
| AAA | 01/07/2018 | 38 | 10000 | 1000 | 129.5 | 600 | 2100 |
| AAA | 01/05/2018 | 44.5 | 10000 | 1000 | 174 | 700 | 2800 |
| AAA | 01/04/2018 | 51 | 10000 | 1000 | 225 | 800 | 3600 |
| AAA | 01/03/2018 | 57.5 | 10000 | 1000 | 282.5 | 900 | 4500 |
| AAA | 01/02/2018 | 64 | 10000 | 1000 | 346.5 | 1000 | 5500 |
| AAA | 01/01/2018 | 70.5 | 10000 | 1000 | 417 | 1100 | 6600 |
| AAA | 01/12/2017 | 77 | 10000 | 1000 | 494 | 1200 | 7800 |
| AAA | 01/11/2017 | 83.5 | 10000 | 1000 | 577.5 | 1300 | 9100 |
| AAA | 01/10/2017 | 90 | 10000 | 1000 | 667.5 | 1400 | 10500 |
| AAA | 01/09/2017 | 96.5 | 10000 | 1000 | 764 | 1500 | 12000 |
| AAA | 01/08/2017 | 103 | 10000 | 1000 | 867 | 1600 | 13600 |
| AAA | 01/07/2017 | 109.5 | 10000 | 1000 | 976.5 | 1700 | 15300 |
I've sorted it, bit of advice, if your calculations are well off, check the formats of your numbers, one was one decimal place out hence made a huge differnce. It was general data type in Excel, changed it to Number and made sure all my numbers in the columns and measures were all the same format and boooom the correct answer.
4 Replies
KH11NDRAnyone cracked it yet? Lol
- KH11NDR
I've sorted it, bit of advice, if your calculations are well off, check the formats of your numbers, one was one decimal place out hence made a huge differnce. It was general data type in Excel, changed it to Number and made sure all my numbers in the columns and measures were all the same format and boooom the correct answer.
Hello everyone.
I have some problem like this post.
However I can't solved.Here the table
Here the fields
It's possible to do it?
tks everyone
Have you solved this? I got the same problem.
