Adding Average Sales for Current Fiscal Year as a Line in Line Chart

[ljx0648]

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.

Link To Sample File 

Any help & tips are appreciated!

Thank you!

[amitchandak · Accepted solution]

@ljx0648 , Please check if the attached file can help. I added three measures and date table

[ljx0648]

Thank 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?