Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

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

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]

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1656641631484.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

7 REPLIES 7
CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1656641631484.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

This one worked like a charm. Thanks 

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

YearWeekStartDayOfWeekObjectIDCalWeekCalendarMonthValuesDimColumnADimColumnBDimColumnCDimColumnEDimColumnC
2024011/1/20241120240120240110.00Time001 LMM901NNPOS
2024011/1/2024112024012024010Time001 KML00MM 
2024011/1/20241120240120240120.00Time002 N9001MM 
2024011/1/2024152024012024010Time002KOABC102MM 
2024028/1/20241120240220240210.00Time001 LMM901  
2024028/1/20241120240220240220.00Time001 KML00  
2024028/1/2024112024022024020Time0003 KML00  
2024028/1/2024162024022024020Time0003 KML00  
2024028/1/20241520240220240210.20Time002 ABC102  
20240315/1/2024112024032024030Time001 LMM901  
20240315/1/20241120240320240312.09Time001 KML00  
20240315/1/2024112024032024030Time0003 KML00  
20240315/1/20241120240320240315.10Time002 N9001  
20240315/1/2024152024032024030Time002 ABC102  
20240422/1/2024112024042024040Time001 LMM901OL 
20240422/1/20241120240420240410.00Time001 KML00MM 
20240422/1/20241120240420240406Time002KON9001MM 
20240422/1/2024152024042024040Time002 ABC102MM 
20240529/1/20241120240520240520.00Time001 LMM901  
20240529/1/2024112024052024050Time001 KML00  
20240529/1/2024112024052024050Time0003 KML00  
20240529/1/2024162024052024050Time0003 KML00  
20240529/1/2024152024052024050Time002 ABC102  
2024065/2/2024112024062024060Time001 LMM901  
2024065/2/20241120240620240610.00Time001 KML00  
2024065/2/2024112024062024060Time0003KOKML00  
2024065/2/2024112024062024060Time002 N9001  
2024065/2/2024152024062024060Time002 ABC102  

 

 

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.