get a average of sum in the past 30 days

[hfyjchkl]

here is a table like

date,       count

02-12-11  1

02-12-11  1

02-12-11  1

02-12-11  1

02-12-12 1

02-12-13 1

so we can aggregate it likes

02-12-11  4

02-12-12 1

02-12-13 1

now i want to know the average of last 2 day's sum should be

(4+1)/2 = 2.5 for the 02-12-12

(1+1)/2 = 1  for the 02-12-13

so can i get a measure to get the average of the sum in the past 30 days

[v-xiaotang]

Hi  @hfyjchkl 

Thanks for reaching out to us.

please try this measure,

Measure = 
var _day=30
var _start=MIN('Table'[date])-30
var _sum=CALCULATE(SUM('Table'[count]),FILTER(ALL('Table'),'Table'[date]>=_start && 'Table'[date]<MIN('Table'[date])))
return DIVIDE(_sum,_day)

Then you can use both types of visual,

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[hfyjchkl]

@v-xiaotang can we avoid the front 30 day's gap. we don't have enought data to support divide 30 in the front of 30 day, So, how could we do it like, divide the day of number until the day to be 30 day. or cut the front 30 day's graph

[hfyjchkl]

let me calrify more the requestment; this is original table.

date,       count

02-12-11  1

02-12-11  1

02-12-11  1

02-12-11  1

02-12-12 1

02-12-13 1
i use the sum for past 30 day.

now i hope could have the 30d moving average. likes below:

i want to draw a red dotted line for the first picture

[tamerj1]

Hi @hfyjchkl 

please try changing the code 1 to 29 and 2 to 30

if it did not work please share a relevant sample of set of row data that I can work with. 

[hfyjchkl]

THAT'S WHAT I DO:

LOOKS IT IS NOT RIGHT

[tamerj1]

@hfyjchkl 

What is the code of the measure used in this chart?

 

[hfyjchkl]

it just sum,

[tamerj1 · Accepted solution]

@hfyjchkl 

Maybe you have more dates than selected in the chart. Not sure if you have any other filters. You can try the following code with and with the REMOVEFILTERS part

30 Days Moving Average =
VAR FisrtVisibleDate =
    CALCULATE ( MIN ( Sheet1[date] ), ALLSELECTED ( Sheet1 ) )
VAR CurrentDate =
    MAX ( Sheet1[date] )
VAR Result =
    CALCULATE (
        SUM ( Sheet1[count] ),
        Sheet1[date] > FisrtVisibleDate,
        Sheet1[date] <= CurrentDate,
        Sheet1[date] >= CurrentDate - 29
    )
RETURN
    DIVIDE ( Result, 30 )

[tamerj1]

Hi @hfyjchkl 
Please try

2 Days Moving Average = 
VAR FisrtVisibleDate = CALCULATE ( MIN ( Sheet1[date] ), ALLSELECTED ( Sheet1 ) )
VAR CurrentDate = MAX ( Sheet1[date] )
VAR Result =
    IF ( 
        CurrentDate <> FisrtVisibleDate,
        CALCULATE ( 
            SUM ( Sheet1[count] ),
            Sheet1[date] <= CurrentDate,
            Sheet1[date] >= CurrentDate - 1,
            REMOVEFILTERS ( Sheet1 )
        )
    )
RETURN
    DIVIDE ( Result, 2 )