cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Rolling one month Average of sum of a column on each day

Hi,

I have a table -

 ID Date SerialNum Qty 1 7/11/2019 10 80 1 7/11/2019 10 100 2 7/11/2019 11 100 3 7/11/2019 12 20 3 7/11/2019 12 30 1 7/12/2019 13 40 1 7/12/2019 13 50 2 7/12/2019 14 40 2 7/12/2019 14 100 3 7/12/2019 15 300 1 7/13/2019 16 20 2 7/13/2019 17 30 3 7/13/2019 18 40 3 7/13/2019 18 10 3 7/13/2019 18 60

I want to find out what is the average number of quantities on particular day(according to the data in above table, rolling 2 days):  The desired output is :

 Rolling avarage 2 days Date 430 7/12/2019 345 7/13/2019

If I break it down in steps, this is what I want as output:

 Sum Per day Rolling avarage 2 days Date 330 7/11/2019 530 430 7/12/2019 160 345 7/13/2019

Currently I'm using this code -

Rolling2Day = CALCULATE(AVERAGE(Sheet1[Qty]),DATESINPERIOD (
'Sheet1'[Date],
LASTDATE ( 'Sheet1'[Date] ),
-2,
DAY
)
)
The output is :
 Date Rolling 2Day 7/11/2019 66 7/12/2019 86 7/13/2019 69

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Rolling one month Average of sum of a column on each day

Hi @Vsna

You can use M code to group table and add index column in query editor:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc9JDsAgCAXQu7A2kcGpZzHe/xrVNpFKrBtC8iB8agUCB9kTeUa6ek/YS0Fo7scIX2SL9EGxOKb5YLJc5GljOBwsLmHUgu5tzQRVjE+Y5aJMTPoFW8v6hVgrmmZrdLDUrd0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, SerialNum = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"SerialNum", Int64.Type}, {"Qty", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Count", each List.Sum([Qty]), type number}}),
in

Then add the measure to generate the rolling average:

```Measure = var lastindex = MAX(Table1[Index])-1
var lastcount = CALCULATE(MAX(Table1[Count]),FILTER(ALL(Table1),[Index]=lastindex))
Return
IF(lastindex=0,BLANK(),DIVIDE(MAX(Table1[Count])+lastcount,2))```

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team

## Re: Rolling one month Average of sum of a column on each day

Hi @Vsna

You can use M code to group table and add index column in query editor:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc9JDsAgCAXQu7A2kcGpZzHe/xrVNpFKrBtC8iB8agUCB9kTeUa6ek/YS0Fo7scIX2SL9EGxOKb5YLJc5GljOBwsLmHUgu5tzQRVjE+Y5aJMTPoFW8v6hVgrmmZrdLDUrd0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, SerialNum = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"SerialNum", Int64.Type}, {"Qty", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Count", each List.Sum([Qty]), type number}}),
in

Then add the measure to generate the rolling average:

```Measure = var lastindex = MAX(Table1[Index])-1
var lastcount = CALCULATE(MAX(Table1[Count]),FILTER(ALL(Table1),[Index]=lastindex))
Return
IF(lastindex=0,BLANK(),DIVIDE(MAX(Table1[Count])+lastcount,2))```

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Rolling one month Average of sum of a column on each day

Thanks Much Dina

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 391 members 3,619 guests
Recent signins: