Running Total - Incorrect Results

[hemantkg]

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.

[Vvelarde · Accepted solution]

@hemantkg

The problem is that you want a calculated column and my answer was to a measure =(

I'm sure that other friends in community can help you. I'm sign out until tomorrow.

Good Luck

[tringuyenminh92 · Accepted solution]

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.

[hemantkg]

Thanks @tringuyenminh92. The following formula gives the correct and expected result for a measure:

TOTALMTD(Sum(Sheet1[Sales]), Sheet1[Date])

[Vvelarde]

@hemantkg

Try using TotalMTD

MTD = TOTALMTD(SUM(Table1[Sales]),Table1[Date])

[hemantkg]

Thanks Victor for quick reply. Tried it, it doesnt even work as expected:

[hemantkg]

The TOTALMTD works now, but it is working same as the other calculation, this is the TOTALMTD formula:

MTD using Total MTD = TOTALMTD(Sum(Sheet1[Sales]), 'Date'[Date], ALLEXCEPT(Sheet1, Sheet1[Sales Person], Sheet1[Geography]))

But we still have the missing previous total for combination mentioned in OP.

[Vvelarde · Accepted solution]

@hemantkg

The problem is that you want a calculated column and my answer was to a measure =(

I'm sure that other friends in community can help you. I'm sign out until tomorrow.

Good Luck