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.
Assuming I have the correction right above, here’s one approach. Because your data is at a monthly granularity, not daily, there are (at least) two reasonable approaches to take with this: 1) pull the FY over from the Dates table via Power Query, or 2) create a virtual relationship between the tables via TREATAS using Month&Year. I opted for the simplicity of #1, but #2 is probably better if you’re going to be doing additional calculations on this Data Table.
Here are the two relevant measures - the first calculates the Max services per FY, and the second calculates your desired metric by FY:
Max Services =
CALCULATE(
MAX( Data[Services] ),
Data[Month] = 6,
ALL( Data),
VALUES( Data[Fiscal Year] )
)
Tot Sales Div Max Svcs =
VAR TotSalbyFY =
CALCULATE(
[Total Sales],
ALLEXCEPT(
Data,
Data[Fiscal Year]
)
)
VAR Quotnt =
DIVIDE(
TotSalbyFY,
[Max Services],
0
)
RETURN
Quotnt
Here’s what it looks like put together:
I hope this is helpful. Full solution file attached
Hi @jgriffit, 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!
