I have the following data where I need to show the quarterly average of a rolling average.
Name Pod Complete Date Days Qtr
16.04.1 AC1 03/23/16 7.06875 Q1 2016
16.04.2 AC1 04/07/16 4.275 Q2 2016
16.05.1 AC1 04/21/16 6.5375 Q2 2016
16.05.2 AC1 05/05/16 8.2472 Q2 2016
16.06.1 AC1 05/18/16 10.50 Q2 2016
16.06.2 AC1 06/03/16 9.514 Q2 2016
16.07.1 AC1 06/15/16 4.919 Q2 2016
16.07.2 AC1 06/29/16 7.459 Q2 2016
16.08.1 AC1 07/13/16 4.106 Q3 2016
16.08.2 AC1 08/04/16 5.259 Q3 2016
16.09.1 AC1 08/17/16 6.687 Q3 2016
16.09.2 AC1 08/31/16 7.566 Q3 2016
16.04.1 AC3 03/23/16 4.908 Q1 2016
16.04.2 AC3 04/06/16 7.454 Q2 2016
16.05.1 AC3 04/20/16 4.869 Q2 2016
16.05.2 AC3 05/04/16 8.548 Q2 2016
16.06.1 AC3 05/19/16 6.874 Q2 2016
16.06.2 AC3 06/02/16 10.157 Q2 2016
16.07.1 AC3 06/16/16 3.969 Q2 2016
16.07.2 AC3 06/29/16 8.207 Q2 2016
16.08.1 AC3 07/13/16 4.106 Q3 2016
16.08.2 AC3 08/04/16 5.259 Q3 2016
16.09.1 AC3 08/17/16 6.687 Q3 2016
16.09.2 AC3 08/31/16 7.566 Q3 2016
I have created a measure that will give me the running average of Day per Name when I use it in a visualization and filter by Pod:
Running Average = Calculate(AVERAGE(MyTime[Day]),filter(ALLSELECTED(MyTime),MyTime[Complete Date]<=max(MyTime[Complete Date])))
I have also tried creating a new table using summarizecolumns so I can group by Pod and Qtr and this will return the quarterly average by pod, but I need the quarterly average of the running average.
Table = summarizecolumns(MyTime[pod],MyTime[Qtr],filter(allselected(MyTime),MyTime[Complete Date]<=max(MyTime[Complete Date])),"QtrAv",average(MyTime[Day]))
So what I'd like to do is somehow add a Running Average column to my 'MyTime' table and then I would think I could create a table using summarizecolumns to get the quarterly average of the running average.
'MyTime' table with Running Average column added
Name Pod Complete Date Days Qtr Running Average
16.04.1 AC1 03/23/16 7.06875 Q1 2016 7.07
16.04.2 AC1 04/07/16 4.275 Q2 2016 5.67
16.05.1 AC1 04/21/16 6.5375 Q2 2016 5.96
16.05.2 AC1 05/05/16 8.2472 Q2 2016 6.53
16.06.1 AC1 05/18/16 10.50 Q2 2016 7.33
16.06.2 AC1 06/03/16 9.514 Q2 2016 7.69
16.07.1 AC1 06/15/16 4.919 Q2 2016 7.29
16.07.2 AC1 06/29/16 7.459 Q2 2016 7.32
16.08.1 AC1 07/13/16 4.106 Q3 2016 6.96
16.08.2 AC1 08/04/16 5.259 Q3 2016 6.79
16.09.1 AC1 08/17/16 6.687 Q3 2016 6.78
16.09.2 AC1 08/31/16 7.566 Q3 2016 6.84
16.04.1 AC3 03/23/16 4.908 Q1 2016 4.91
16.04.2 AC3 04/06/16 7.454 Q2 2016 6.18
16.05.1 AC3 04/20/16 4.869 Q2 2016 5.74
16.05.2 AC3 05/04/16 8.548 Q2 2016 6.44
16.06.1 AC3 05/19/16 6.874 Q2 2016 6.55
16.06.2 AC3 06/02/16 10.157 Q2 2016 7.15
16.07.1 AC3 06/16/16 3.969 Q2 2016 6.70
16.07.2 AC3 06/29/16 8.207 Q2 2016 6.89
16.08.1 AC3 07/13/16 4.106 Q3 2016 6.88
16.08.2 AC3 08/04/16 5.259 Q3 2016 6.73
16.09.1 AC3 08/17/16 6.687 Q3 2016 6.69
16.09.2 AC3 08/31/16 7.566 Q3 2016 6.78
The result I'm looking for would look like:
Qtr pod QtrRunAv
Q1 2016 AC1 7.07
Q1 2016 AC3 4.91
Q2 2016 AC1 6.83
Q2 2016 AC3 6.52
Q3 2016 AC1 6.84
Q3 2016 AC3 6.77
Since I'm just learning Power BI and DAX, I'm not quite sure how to achieve the result I need. Can someone possibly provide some assistance?
