Forum Discussion
Running Total - Incorrect Results
I have searched the forum for possible solution to below scenario, could not find any leads.
Following is the data snapshot:
| Date | Sales Person | Geography | Sales |
| 1/1/2017 | A | X | 10 |
| 1/1/2017 | A | Y | 10 |
| 1/1/2017 | A | Z | 10 |
| 1/1/2017 | B | X | 10 |
| 1/1/2017 | B | Y | 10 |
| 1/1/2017 | B | Z | 10 |
| 1/1/2017 | C | X | 10 |
| 1/1/2017 | C | Y | 10 |
| 1/1/2017 | C | Z | 10 |
| 1/2/2017 | A | X | 10 |
| 1/2/2017 | A | Y | 10 |
| 1/2/2017 | A | Z | 10 |
| 1/2/2017 | B | X | 10 |
| 1/2/2017 | B | Y | 10 |
| 1/2/2017 | B | Z | 10 |
| 1/2/2017 | C | X | 10 |
| 1/2/2017 | C | Y | 10 |
| 1/2/2017 | C | Z | 10 |
| 1/3/2017 | A | X | 10 |
| 1/3/2017 | A | Y | 10 |
| 1/3/2017 | A | Z | 10 |
| 1/3/2017 | B | X | 10 |
| 1/3/2017 | B | Y | 10 |
| 1/3/2017 | B | Z | 10 |
| 1/3/2017 | C | X | 10 |
| 1/3/2017 | C | Y | 10 |
| 1/3/2017 | C | Z | 10 |
| 1/4/2017 | A | X | 10 |
| 1/4/2017 | B | X | 10 |
| 1/4/2017 | C | X | 10 |
| 1/4/2017 | A | Y | 10 |
| 1/4/2017 | B | Y | 10 |
| 1/4/2017 | C | Y | 10 |
| 1/4/2017 | A | Z | 10 |
| 1/4/2017 | B | Z | 10 |
Notice how C Sales person for Z geography is missing for 1/4/2017.
This is the calculation for calculating MTD value:
MTD = CALCULATE(SUM(Sheet1[Sales]), ALLEXCEPT(Sheet1, Sheet1[Sales Person], Sheet1[Geography]), Sheet1[Date] <= EARLIER(Sheet1[Date]), DATESMTD('Date'[Date]))
This is the output generated:
| Date | Sales | MTD |
| 1/1/2017 | 90 | 90 |
| 1/2/2017 | 90 | 180 |
| 1/3/2017 | 90 | 270 |
| 1/4/2017 | 80 | 320 |
As you see the result expected for 1/4/2017, is 270+80=350. But what is happening here is that since C, Z combination is missing for 1/4/2017, the previous sum of this combination is missed and the result is 320.
How will I get the expected result? Thanks in advance.
- Hi hemantkg,
It's 320 cause you are using calculated column. Behind the scenes, it iterates row by row and cumulatives sum values, but there are 8 rows in April when it should be 9 rows as another months. My recommendation is using calculated measure.
6 Replies
- Hi hemantkg,
It's 320 cause you are using calculated column. Behind the scenes, it iterates row by row and cumulatives sum values, but there are 8 rows in April when it should be 9 rows as another months. My recommendation is using calculated measure.Thanks tringuyenminh92. The following formula gives the correct and expected result for a measure:
TOTALMTD(Sum(Sheet1[Sales]), Sheet1[Date])
