How to Calculate Sum and Average in Power Pivot Data Model

[happy1001]

Hello

I have got stock market data in a Table, which I want to import inside Power Pivot Data Model and then create some new columns.

I have prepared a very small sample excel file. The real data is quite large in size. And I have also prepared a very SIMPLE Example for learning the method of how we can calculate CHANGE and PERCENTAGE CHANGE COLUMNS inside PP Data Model.

Once these 2 new columns are calculaed, then I also want to calculate these 4 new columns as well, if that is not very hard to do -

Past 2 Day Sum of ClosePrice
Past 3 Day Sum of ClosePrice
Past 2 Day Average of ClosePrice
Past 3 Day Average of ClosePrice

I have attached the excel file that has got -
Source Data Table
Expected Data Table
and the data has been imported into the Data Model.

I do not have any experience in dealing with Data Models yet. Please suggest how we can get the desired output inside Data Model. I am not completely sure, if such calculations can be done inside Data Model or not, but I think it should be possible, either directly or by using some complex DAX Codes etc.

I am using the following software versions -
Microsoft SQL Server Management Studio version- 12.0.2000.8,
Microsoft Office Professional Plus 2016 x64
and Windows 7 x64

Thanks a lot for any help.

Regards

 

[happy1001]

 

[happy1001]

I do not know why but I am facing a lot of difficulty in adding attachments and snaps ! The attachment button is not working properly. That's why I have to add the files in multiple posts. Still not being able to attach the excel sheet for this thread.

 

[happy1001]

Finally, I had to upload it on dropbox.

https://www.dropbox.com/s/buti2ggh1r9qi0s/Change And Percentage Change.xlsx?dl=0