Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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! |
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! |
This one worked like a charm. Thanks
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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 |
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.
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |