Hi,
Thanks for anyone that takes the time to read this, apologies it's long. First time posting. I am new to Power BI. I am creating a report which will show the number of orders of a product per quarter. I have date fields (year, quarter) a count of a field (to show the number of orders), a field for Previous Year, YTD, YTD this time last year, Bgt, F'cst. Bgt and F'cst are imported in via CSV files while most others are measures. The issue I am having is;
The report needs to use academic quarters and year, where Sep-Nov30 =Q1 Dec-Feb=Q2 Mar-May31=Q3 Jun-Aug31=Q4.
Where Sep-Nov 2017 = Q1-2018, Dec2017-Feb2018 = Q2-2018, Mar2018-May2018= Q3-2018, Jun2018-Aug2018=Q4-2018
Term: Sep-Feb (end of) = H1 Mar-Aug = H2
I created a date table which is linked to the main table based on order date, and defined the academic date fields using DAX;
My date table is called calendar (only realized late on that I spelt it wrong ha)
Term = IF(Calandar[Month Number]>2 && Calandar[Month Number]<9, "H2", "H1")
Academic Quarter = IF(Calandar[Month Number]>=9 && Calandar[Month Number]<=11, "Q1", IF(Calandar[Month Number]>=12 || Calandar[Month Number]<=2, "Q2", IF(Calandar[Month Number]>=3 && Calandar[Month Number]<=5, "Q3", "Q4")))
Academic Year = if(Calandar[Month Number]>8, Calandar[Year]+1, Calandar[Year])
Academic Qtr Yr= Calandar[Academic Quarter] & "-" & Calandar[Academic Year]
Problem I have is that my measures only work based on the calandar year not for the defined Academic Year.
Is there any way around this?
I also cannot get YTD This Time Last Year working (regardless of calendar date or academic date).
Thanks
