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

[Anonymous]

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

Thanks in advance.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

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}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1)
in
#"Added Index"

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

[Anonymous]

Thanks Much Dina 🙂