SUM every 8 days value from today to last 6 months

[ALEX2011ALfer_]

• Hi guys,

I wonder if it is possible to SUM every 8 days value from today to the last 6 months? Using Dax

* the type of the value is discrete so I need to take just one value for every 8 days, not the sum of the 8 days. 

* i can use filter to specify the last 6 months. Not necessary to do that in DAX 

Thanx !! 

🤝💜

@RicoZhou

please help. 

[v-xiaotang · Accepted solution]

Hi @ALEX2011ALfer_ 

>> to SUM every 8 days value from today to the last 6 months

You can try this, create the measures below

Measure1 = 
var _n=8
var _diff=DATEDIFF(MIN('table'[Date]),TODAY(),DAY)
return mod(_diff,_n)

Measure2 = CALCULATE(SUM('table'[Index]),FILTER(ALL('table'),'table'[Date]<TODAY() && 'table'[Date]>= EDATE(TODAY(),-6) && [Measure1]=0))

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.

[amitchandak]

@ALEX2011ALfer_ , You have to create that column in Table

quotient( -1* Datediff(today(), [Date], day),8)+1

This will create a partition you can use in group by/row/axis etc .

you can use a measure like

Rolling 6 Sales =
var _max = today()
var _min = date(year(_max), month(_max)-6,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))