Dax

Question

Hi ,I have a dataset with one of its column as date and one more as close price&nbsp;,I wanted to find the average&nbsp;of close price&nbsp;for the last 7 dates present in my date column for every row.For egDateClose Price7Day15Day30day8-Apr-1614.1214.1113.6566713.811677-Apr-1614.2414.0813.6186713.7816-Apr-1614.0613.8842913.5906713.751335-Apr-1613.7813.7328613.6006713.7214-Apr-1614.2713.6842913.62213.7051-Apr-1614.1513.5657113.6206713.6743331-Mar-1614.1513.4042913.64613.66130-Mar-1613.9113.2428613.66813.6643329-Mar-1612.8713.1671413.7406713.65428-Mar-161313.2642913.8833313.65523-Mar-1613.4413.3814313.93613.6783322-Mar-1613.4413.4914313.9406713.7353321-Mar-1613.0213.5857113.9446713.8123318-Mar-1613.0213.7614313.9633313.90333&nbsp;Thanks,Rashmita Reddy

sean · Answer

RashmitaR&nbsp;This Measure should work... Let me know.&nbsp;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
)&nbsp;

achinm45 · Answer

Hi RashmitaRTry following :&nbsp;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 :&nbsp;3) Write similarly for other metrics&nbsp;Cheers !Achin

achinm45 · Answer

Hi Sean,Thanks for perspective. However dates are not contigous :)&nbsp;BR,Achin&nbsp;

rashmitar · Answer

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.

Forum Discussion

Dax

4 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX

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