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.
Hello,
I have create a calendar table to build relationship between my sales date and the calendar. I need an average for the past 3 month from now( MAY) in this case I'm needing FEB-MAR- APR.
HISTORICAL SALES HISTORICAL SALES IN % AVERAGE P3M SALES IN %
feb-19 | mar-19 | abr-19 | may-19 | Day | feb-19 | mar-19 | abr-19 | AVG | ||
210 | 89 | 284 | 1 | 4% | 1% | 5% | 3% | |||
267 | 175 | 172 | 2 | 9% | 4% | 8% | 7% | |||
123 | 186 | 278 | 3 | 11% | 7% | 13% | 9% | |||
288 | 212 | 169 | 4 | 17% | 11% | 16% | 13% | |||
95 | 171 | 197 | 5 | 18% | 14% | 19% | 16% | |||
115 | 264 | 230 | 6 | 21% | 18% | 23% | 21% | |||
108 | 287 | 209 | 7 | 23% | 23% | 27% | 24% | |||
131 | 200 | 252 | 8 | 25% | 26% | 31% | 28% | |||
187 | 298 | 233 | 9 | 28% | 31% | 35% | 31% | |||
238 | 223 | 248 | 10 | 33% | 35% | 39% | 34% | |||
200 | 229 | 208 | 11 | 37% | 39% | 43% | 37% | |||
202 | 97 | 133 | 12 | 40% | 40% | 45% | 41% | |||
181 | 168 | 123 | 13 | 44% | 43% | 48% | 44% | |||
248 | 286 | 243 | 14 | 48% | 48% | 52% | 48% | |||
236 | 145 | 232 | 15 | 53% | 50% | 56% | 52% | |||
260 | 292 | 218 | 16 | 58% | 55% | 60% | 55% | |||
264 | 87 | 113 | 17 | 63% | 57% | 62% | 58% | |||
128 | 144 | 187 | 18 | 65% | 59% | 65% | 62% | |||
177 | 217 | 286 | 19 | 68% | 63% | 70% | 66% | |||
200 | 241 | 96 | 20 | 72% | 67% | 71% | 69% | |||
101 | 255 | 112 | 21 | 74% | 71% | 73% | 71% | |||
184 | 93 | 148 | 22 | 77% | 72% | 76% | 74% | |||
295 | 163 | 95 | 23 | 83% | 75% | 78% | 77% | |||
188 | 212 | 187 | 24 | 86% | 79% | 81% | 81% | |||
143 | 279 | 190 | 25 | 89% | 83% | 84% | 84% | |||
137 | 90 | 197 | 26 | 92% | 85% | 88% | 87% | |||
263 | 82 | 215 | 27 | 97% | 86% | 91% | 90% | |||
183 | 273 | 86 | 28 | 100% | 91% | 93% | 93% | |||
152 | 292 | 29 | 93% | 98% | 97% | |||||
117 | 123 | 30 | 95% | 100% | 100% | |||||
291 | 31 | 100% | ||||||||
Solved! Go to Solution.
Hi,
See if the results here are correct. Download the PBI file from here.
Hi,
I am not clear about which are your input tables and what exact output are you expecting? Additionally, share the link from where i can download your Excel workbook so that i see your Excel formulas.
In excel this are my formulas:
Im Trying to do pretty much the same. By the moment I have been using the following table format:
What I need is to calculate the average of the past 3 months, but taking in consideration the last day of each month, for example for february it should have the average of de day 28, plus march 31, plus april 30, by the moment it is shown this way:
And i need it tobe shown same as in the excel because of the amount of days in each month.
I dont know if it is possible. Or maybe another option is calculating the percentage of a day in a month 1/30, 2/30, 3/30 .... and looking for the closest value and return it as an average.
Note: data may not be according to the others due to a "RAND()" to return values as sales.
Help please!
@Ashish_Mathur wrote:Hi,
I am not clear about which are your input tables and what exact output are you expecting? Additionally, share the link from where i can download your Excel workbook so that i see your Excel formulas.
Hi,
I can try to help if you can share the link from where i can download the PBI file.
https://drive.google.com/file/d/1neda9_ucifFnelem-z7iYXfeYCYIsQwL/view?usp=sharing
This is an example of the data. What I'm needing is to do the same as it is in excel with the % average for the past 3 months. Let me know if it is possible!
Hi,
See if the results here are correct. Download the PBI file from here.
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 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |