Average for 3 months IN MEASURE

[Anonymous]

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%

I have used this in excel and I'm trying to do tha same. That it will take the last day of each month to calculate the average, is there any way I can do that and display it in a  MATRIX in power BI  if I filter my report per month. (currently in MAY)

Currently Power BI automatically  generates a 100% for the "30 of february" which  obviously doesn't exist.  

Thanks in advance 

[Ashish_Mathur]

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.  

[Anonymous]

In excel this are my formulas: 

For the historical sales in % : =IFERROR(BZ12/SUM(BZ$12:BZ$39);"")And I ask for an average of the 3 last months sales in %

Im Trying to do pretty much the same. By the moment I have been using the following table format:

It has many rows of same date because of another column that is not shown in here, but it has values from 2016 to end of current month.

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.  

---

[Ashish_Mathur]

Hi,

I can try to help if you can share the link from where i can download the PBI file.

[Anonymous]

Spoiler

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!

[Ashish_Mathur · Accepted solution]

Hi,

See if the results here are correct.  Download the PBI file from here.