Drill Down from Financial Year to month starting with first month in financial year
We have a financial year starting July 1 Yearn ending Jun 30 Yearn+1
Financial year is calculated by: FY = YEAR(DATEADD(month, 6, rev_timein))
Then, use date parts OR the Date Function to get the Quarter (optional), Month, Day.
The problem is that the System sorts Month by FY, but then when drilling down, it sorts by calendar month so the output is, for example FY 2020:
Jan2020, Feb2020, Mar2020, Apr2020, May2020, Jun2020, Jul2019, Aug2019, Sept2019, Oct2019, Nov2019, Dec2019.
Obviously, it is ideal if it sorts:
Jul2019, Aug2019, Sept2019, Oct2019, Nov2019, Dec2019, Jan2020, Feb2020, Mar2020, Apr2020, May2020, Jun2020
If I insert a stand alone YEAR column, then when drilling down:
Year 2019 2020
metric agg2019 agg2020 <--- We want this still aggregated for FY 2020, not broken out by year.
Thank you for any assistance.