Dynamic time period based on number of weeks / months

Anything related to PowerPivot and DAX Formuale
kyukazu92
Posts: 1
Joined: Wed Jul 29, 2020 5:56 pm

Dynamic time period based on number of weeks / months

Postby kyukazu92 » Wed Jul 29, 2020 6:20 pm

Dear all,
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!
Best, kyukazu92
Attachments
2_Parameter Table.PNG
2_Parameter Table.PNG (4.55 KiB) Viewed 244 times
1_Data_Stock & Returns.PNG
1_Data_Stock & Returns.PNG (65.46 KiB) Viewed 244 times

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests

cron