Average of 5 consecutive daily prices of a given product

DAX DAX Calculations
1

I am looking for the DAX measure that will calculate the following:

The average 5 daily prices of a given product if:
(a) transaction date is Day 0, e.g. if transaction date is 12/23/2019, day 1 is 12/24/2019, day 2 is 12/25/2019, day 3 is 12/26/2019, day 4 is 12/27/2019 and day 5 is 12/28/2019 - and if any of these dates is not in the pricing table, to use the next available date

(b) transaction date is Day 3 - using the transaction date in (a), day 1 is 12/21/2019, day 2 is 12/22/2019, day 4 is 12/24/2019 and day 5 is 12/25/2019 - and if the transaction date is not in the pricing table, to use the 3 consecutive dates before the transaction date and 2 consecutive days after the transaction date. Also, if any of the other dates is not in the pricing table, to use the next available date.

© transaction date is Day 6, e.g. using the transaction date in (a), day 1 is 12/18/2019, day 2 is 12/19/2019, day 3 is 12/20/2019, day 4 is 12/21/2019 and day 5 is 12/22/2019, and if any o these dates is not in the pricing table, to use the next available date.

Thank you!

2

Hi @melaneelee .

To help us further visualize your issue, could you please provide as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Greg

3

Hi @melaneelee, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!