I am struggling with my DAX and I hope someone can provide me with a fix or some ideas on how to solve my issue:
I have a column with weekly & end-of-month stock returns (e.g. apple stock, see attached) for the last 10 years. Additionally, I have prepared a sort of parameter table (DIM_Months, DIM_Weeks) and as well a DIM_Periods table that contains my date period, day of the week etc.
What I would like to do is simply to create a measure that returns the sum of all returns, depending on:
- chosen day of the week (e.g. only Fridays)
- chosen start year (e.g. if I choose 2018 in a slicer, the MAX Date / starting date should be the last Friday in December 2018)
- chosen length (e.g. include the last 52, 104 or 260 weeks (respectively months starting from the last Friday in December 2018 in the calculation).
The problem is that depending on which day of the week you choose, you`ll get 53 data points per year (gap year etc.). However, the amount of data points I need is fixed to multiples of 52.
I've tried to figure out a solution with COUNTROWS, but couldn't get my formula running.
I would really appreciate some help on this matter!
Anything related to PowerPivot and DAX Formuale
1 post • Page 1 of 1
- 2_Parameter Table.PNG (4.55 KiB) Viewed 244 times
- 1_Data_Stock & Returns.PNG (65.46 KiB) Viewed 244 times
Who is online
Users browsing this forum: No registered users and 4 guests