Dax

[RashmitaR]

Hi ,

I have a dataset with one of its column as date and one more as close price ,I wanted to find the average of close price for the last 7 dates present in my date column for every row.For eg

Date	Close Price	7Day	15Day	30day
8-Apr-16	14.12	14.11	13.65667	13.81167
7-Apr-16	14.24	14.08	13.61867	13.781
6-Apr-16	14.06	13.88429	13.59067	13.75133
5-Apr-16	13.78	13.73286	13.60067	13.721
4-Apr-16	14.27	13.68429	13.622	13.705
1-Apr-16	14.15	13.56571	13.62067	13.67433
31-Mar-16	14.15	13.40429	13.646	13.661
30-Mar-16	13.91	13.24286	13.668	13.66433
29-Mar-16	12.87	13.16714	13.74067	13.654
28-Mar-16	13	13.26429	13.88333	13.655
23-Mar-16	13.44	13.38143	13.936	13.67833
22-Mar-16	13.44	13.49143	13.94067	13.73533
21-Mar-16	13.02	13.58571	13.94467	13.81233
18-Mar-16	13.02	13.76143	13.96333	13.90333

Thanks,

Rashmita Reddy

[achinm45]

Hi @RashmitaR

Try following :

1 ) Create an Index column by going into Power Query part of Power BI (click Edit Queries) and then under transformmations.

2) Then write formula as in screenshot here :

3) Write similarly for other metrics

Cheers !

Achin

[Sean]

@RashmitaR This Measure should work... Let me know.

7 Day Avg =
DIVIDE (
    CALCULATE (
        SUM ( Table[Close Price] ),
        DATESINPERIOD ( Table[Date], LASTDATE ( Table[Date] ), -7, DAY )
    ),
    CALCULATE (
        DISTINCTCOUNT ( Table[Date] ),
        DATESINPERIOD ( Table[Date], LASTDATE ( Table[Date] ), -7, DAY )
    ),
    0
)

[RashmitaR]

Thank you for reply. I have created a column using your suggestion but while creating another one for 15day average i am getting a circular dependency hence i am unable to create it . Could you please help me by letting me know how to deal with this issue .

Thanks ,

Rashmita Reddy.

[achinm45]

Hi @Sean,

Thanks for perspective. However dates are not contigous 🙂

BR,
Achin