Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
Thanks @tringuyenminh92. The following formula gives the correct and expected result for a measure:
TOTALMTD(Sum(Sheet1[Sales]), Sheet1[Date])
Thanks Victor for quick reply. Tried it, it doesnt even work as expected:
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.
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
User | Count |
---|---|
103 | |
87 | |
77 | |
70 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |