Forum Discussion
michael_knight
Post Prodigy
Post ProdigyFiscal Year - April to March
Hi, I've been stuck on a project for a while and I've had a problem which I thought was simple but I'm having some problems with. I'm working with data from 2015 and I want to show the fiscal yea...
A column with this cal should allow you to sort perfectly across year and month ranges. The only problem is that the date column of this table will not sort on this. You might have to create a month-Year Col for display
SortCol = CONCATENATE(if([Date].[MonthNo]> 3,DateTable[Date].[Year],DateTable[Date].[Year]-1) ,format(if(DateTable[Date].[MonthNo]> 3,DateTable[Date].[MonthNo]-3,DateTable[Date].[MonthNo]+9),"00"))
Please download the file again from dropbox, which I uploaded. I have corrected fiscal Year cal, created month-year and sort col. Changed the graph and the year filter.
The way how I approach FY is to create table for 'central' date dimension (Modeling, New Table).
Once created, I add relations to all other date columns of other tables.
Usually I have more columns (for variety of use cases), but I have stripped them out for clarity:
dim_Date = ADDCOLUMNS (
CALENDAR (DATE(2000;4;1); DATE(2030;3;31));
"Year"; YEAR ( [Date] );
"Yr-Q"; FORMAT ( [Date]; "YY-\QQ" );
"Yr-Mo"; FORMAT ( [Date]; "YY-MM" );
"FY"; "FY" & FORMAT ( EDATE([Date];-3); "YY" );
"FY_rel"; YEAR(EDATE([Date];-3)) - YEAR(EDATE(TODAY();-3));
"mnth_rel"; YEAR([Date])*12+MONTH([Date])-YEAR(TODAY())*12-MONTH(TODAY());
"Months ago"; var m=[Date]-TODAY() return if(m<-90;"3 mnths or older";if(m<-60;"2-3 mnths";if(m<-30;"1-2 mnths";"...-1 mnth")))
)