Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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 )
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.
@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
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
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.
THAT'S WHAT I DO:
LOOKS IT IS NOT RIGHT
it just sum,
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 )
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 )
User | Count |
---|---|
98 | |
89 | |
82 | |
70 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |