Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |