Find the rolling for 12 weeks average and standard deviation from Last Date

[Anonymous]

I need to create Rolling AVG and Standard Deviation from LAST DATE(weekly, [not monthly])

I will provide the WEEK and WEEKLY ORDERS(below). But I am having trouble to create the Rolling AVG and Standard Deviation in power bi.

Here is what the results should look like: 

WEEK	WEEKLY ORDERS	Results ->	Rolling AVG		Standard Deviation
10/19/2020	35,897		27,750		12964.03086
10/12/2020	35,786		26,493		12900.41016
10/5/2020	6,996		25,083		12801.7815
9/28/2020	21,720		25,613		12114.93206
9/21/2020	30,439		27,262		12970.73729
9/14/2020	17,325		27,008		12935.62564
9/7/2020	11,699		26,930		13001.28962
8/31/2020	46,739		28,144		12170.85556
8/24/2020	27,335		25,121		12047.62601
8/17/2020	52,396		23,439		13185.39
8/10/2020	27,231		20,120		10431.91936
8/3/2020	19,000		19,711		10232.26567
7/27/2020	28,192		21,651		12273.14011
7/20/2020	19,555		20,967		12125.03369
7/13/2020	17,449		19,705		13099.37299
7/6/2020	13,884		19,588		13128.03323
6/29/2020	43,167		18,807		13780.74356
6/22/2020	27,131		16,552		11704.75671
6/15/2020	16,318		16,112		11376.95156
6/8/2020	27,475		15,747		11445.4791
6/1/2020	7,437		15,012		10924.51749
5/25/2020	5,469		15,511		10695.51914
5/18/2020	9,248		17,273		10790.53386
5/11/2020	21,918		18,462		10683.65595
5/4/2020	44,218		20,265		13033.46129
4/27/2020	19,306		22,516		18776.50541
4/20/2020	3,145		25,183		20651.43724
4/13/2020	15,925		25,978		19918.49212
4/6/2020	3,738		25,881		19973.95634

These are the formulas i used in excel for finding the AVG and standard deviation: 

Rolling Average formula Used in excel = AVERAGE(C3:C15) [12 weeks=12 rows]
Standard Deviation formula Used in excel = STDEV.S(C3:C15) [12 weeks=12 rows]

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[CNENFRNL · Accepted solution]

[Anonymous]

This one worked like a charm. Thanks 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[PowerBi_1404]

Hi Ashish.

Greetings. 

can you please help me with the same sample power bi file. not able to download. 

I have similar requirement.

Thanks in advance.

[Ashish_Mathur]

Hi,

I do not have that file.  Share the download link of your PBI file, explain the question and show the expected result. 

[PowerBi_1404]

Need Help in DAX for Getting 7 weeks Rolling Average & Standard Deviation for provided Data Format. dates are not continous.

Thursday

Greetings. 

I am trying to get 7 weeks Rolling Average based on the data which has only YearWeek and StartDayofWeek as date columns. The requirement is that the 25 days Rolling Average should be calulated at diffrenent Dimension Columns selected in table.

CalendarMonth, ObjectID, DimColumnA, DimColumnB, DimColumnC, DimColumnE, DimColumnC, AverageRollingSum

I tried with Many Dax, but not giving me the expected rolling Average, always gives the SUM as result. Not really sure if its beacuse of the data. below is the Sample data. Values is the column that needs to be considered for rolling Average.

YearWeek	StartDayOfWeek	ObjectID	CalWeek	CalendarMonth	Values	DimColumnA	DimColumnB	DimColumnC	DimColumnE	DimColumnC
202401	1/1/2024	11	202401	202401	10.00	Time001		LMM901	NN	POS
202401	1/1/2024	11	202401	202401	0	Time001		KML00	MM
202401	1/1/2024	11	202401	202401	20.00	Time002		N9001	MM
202401	1/1/2024	15	202401	202401	0	Time002	KO	ABC102	MM
202402	8/1/2024	11	202402	202402	10.00	Time001		LMM901
202402	8/1/2024	11	202402	202402	20.00	Time001		KML00
202402	8/1/2024	11	202402	202402	0	Time0003		KML00
202402	8/1/2024	16	202402	202402	0	Time0003		KML00
202402	8/1/2024	15	202402	202402	10.20	Time002		ABC102
202403	15/1/2024	11	202403	202403	0	Time001		LMM901
202403	15/1/2024	11	202403	202403	12.09	Time001		KML00
202403	15/1/2024	11	202403	202403	0	Time0003		KML00
202403	15/1/2024	11	202403	202403	15.10	Time002		N9001
202403	15/1/2024	15	202403	202403	0	Time002		ABC102
202404	22/1/2024	11	202404	202404	0	Time001		LMM901	OL
202404	22/1/2024	11	202404	202404	10.00	Time001		KML00	MM
202404	22/1/2024	11	202404	202404	06	Time002	KO	N9001	MM
202404	22/1/2024	15	202404	202404	0	Time002		ABC102	MM
202405	29/1/2024	11	202405	202405	20.00	Time001		LMM901
202405	29/1/2024	11	202405	202405	0	Time001		KML00
202405	29/1/2024	11	202405	202405	0	Time0003		KML00
202405	29/1/2024	16	202405	202405	0	Time0003		KML00
202405	29/1/2024	15	202405	202405	0	Time002		ABC102
202406	5/2/2024	11	202406	202406	0	Time001		LMM901
202406	5/2/2024	11	202406	202406	10.00	Time001		KML00
202406	5/2/2024	11	202406	202406	0	Time0003	KO	KML00
202406	5/2/2024	11	202406	202406	0	Time002		N9001
202406	5/2/2024	15	202406	202406	0	Time002		ABC102

[Ashish_Mathur]

Hi,

I am not sure how much i can help.  Show the expected result clearly.  It would be ideal if you can share the download link of the Excel file with 2 tabs - input and expected result.