14 Day Average Sales (Measure)

[Bennets04]

Good afternoon all,

I have a table with a DATE Column & SALES Column... I'm trying to write a measure formula to calculate the last 14 average sales.

I'm looking for it to automatically calculate the measure like the following (Today is 25th June... So I'm looking for it to return the AVERAGE sales from 11th to 24th)

I have searched high and low but still can't seem to get something that works... Can anyone help

Cheers
Steve

 

[Matt Allington]

You need to write a Time Intelligence function. You should get a calendar table and also read my article here Time Intelligence Using DAX - Excelerator BI

you will need something like this.
=CALCULATE(sum(fact[value]),datesinperiod(calendar[date],max(calendar[date]),-14))

 

[Bennets04]

Thank you for your reply.

In the end i just did a measure for SUM of last 14 days then put a / 14 on the end and got the result i needed