So my data table is like this
Quarter Sales Services
Sep-18 120 10
Dec-18 130 11
Mar-20 140 12
Jun-20 150 13
Sep-19 160 14
Dec-19 170 15
Mar-20 180 16
Jun-20 190 17
And I need some dax to be able to create a formula that sums all of the sales for the financial year, and divides it by the number of services as at the June quarter only.
So for 18-19 I need = sum(120, 130,140, 150) / 13
For 19-20 I need = sum(160, 170,180,190) / 17
I am thinking now that I need to use variables, and max for date.
The data is pre-aggregated so counts of customer ID etc doesn’t work.
YTD formulas don’t work as it sums all of the services over the full financial year. Whereas I only need it at the end of the financial year, and preferably in a way that makes the financial year flexible so I can filter on it.
Any assistance would be appreciated.
Thanks in advance.