Cumulative average without using "datesinperiod" function

[CloudMonkey]

Hi,

I'm trying to understand filter context and one of my challenges is to write a rolling average formula without using "datesinperiod". Please can you tell me how write a 3 day rolling average? I've tried the below code but I don't know how to do the "greater than" part of the formula:

Thanks for any help!

CM

[v-xicai]

Hi @CloudMonkey ,

You can try to create measures like DAX below.

Period End = LASTDATE('Product'[Date])
 
Period Start= FIRSTDATE( DATESINPERIOD('Product'[Date], [Period End], -3, DAY))
 
Rolling 3 Day Avg = CALCULATE(AVERAGE('Product'[Volume]),DATESBETWEEN ( 'Product'[Date], [Period Start], [Period End] ))

Best Regards,

Amy

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

[CloudMonkey]

Is it impossible to do without the "datesinperiod" function? Please can you tell me why?

[v-xicai]

Hi @CloudMonkey ,

Does the formulas above make sense? Could you please tell me the reason why you don't want to use DATESINPERIOD function?

Best Regards,

Amy

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

[Anonymous]

 hi v-xicai

I would like to know an alternative for DATESINPERIOD since my data is not at day level and I dont have a date table.

Could you help please?

[CloudMonkey]

Hi @v-xicai ,

It makes sense but I don't feel comfortable that I am unable to do the calculation without the "datesinperiod" function. I don't feel comfortable just blindly using built in filters, hope that makes sense? (happy to use built in functions later on when I know what I'm doing to tidy up the code)

CM