Forum Discussion
Helper IIIAdding Average Sales for Current Fiscal Year as a Line in Line Chart
Dear Community Members,
I am trying to implement a average sales line (ONLY for current year) inside a daily sales line chart.
Line Chart:
X Axis: Date Dimension
Y Axis: Sum of Sales
Requirement:
- I need to add a line that represents the "Average Sales For Current Fiscal Year" - the 1st day of every fiscal starts on Nov 1
- For the result, I am looking for a line calculated as -
- Sum of inflows from Nov 1 = $330320
- Distinct Date in the period (from Nov 1 onwards only count the dates with sales) = 70
- $330320/70 = $4719
PBI File with sample data attached for reference.
Any help & tips are appreciated!
Thank you!
ljx0648 , Please check if the attached file can help. I added three measures and date table
2 Replies
- amitchandak
Super User
ljx0648 , Please check if the attached file can help. I added three measures and date table
ljx0648Thank you for your prompt response!
The number is really close to what is needed, however, the number calculated in the file provided is slightly off with the number I calculated
- $330320/70 = $4719
I tried to modify the ENDOFYEAR date in Avg till year end 1 formula to "10/31“ （as new fiscal starts on Nov 1） but it did not align with the calculation as well.
May I know if there is any modification can be made to get the numbers as I did?