Hello,
The below visualisation comes from a transaction dataset of orders, joined to a custom date table "tbl_FYCalendar" which provides fields for fiscal year and fiscal month index, as well as calendar years and months.
I was tasked with calculating the raw and & change YOY for each Department's number of orders.
I did this successfully to begin with - I created a Matrix visual, and used measures like
LastFYOrders = CALCULATE([#Orders],PREVIOUSYEAR(tbl_FYCalendar[Date],"03/31"))
(which I realise kind of duplicates what the custom calendar is meant to do - I couldn't figure out how to get the measure to refer to the fiscal year dynamically).
and then
%OrdersChangeYOY = IF(NOT(ISBLANK([LastFYOrders])),([#Orders]/[LastFYOrders])-1)
(again, I know the "if(not(isblank)))" bit isn't very elegant either - I couldnt' work out how else to stop it trying to calculate the change from nothing to FY18-19.
I then realised that I wasn't comparing like with like, as we only have data up until the end of October for the latest fiscal year.
Therefore, I applied a filter to the visual, on calendar month - so that only orders from April to October in each fiscal year were to be included.
And I got this:
As you can see, the Orders Change and % Orders Change columns are not correct - they are referring to the full previous year's totals, not just the April-Oct versions.
I can see why, but I am stuck on how to fix it.
Any ideas much appreciated - thanks in advance.
