So I am using the Power BI Date Table Code you provide. My Fiscal year starts July 1 of a year and ends June 30 of the next year. I am using MonthName but I need to be able to order it in visuals as July, August, September, October, November, December, January, February, March, April, May, June. How do I do this? I tried using Switch True but it says I cannot use a sorted column to sort another column.
I add the following columns to my Date table (Sam’s code). I also included the sort order for each column at the end of the code
Short Month = LEFT ( Dates[MonthName] , 3 ) - Sort by MonthOfYear Quarter = LEFT ( Dates[Quarter & Year] , 2 ) - Sort by QuarterOfYear Fiscal Year = IF( Dates[MonthOfYear] <= 6, Dates[Year], Dates[Year] + 1 ) - Default Sort FiscalMonthNo. = IF( MOD( Dates[MonthOfYear] + 6, 12 ) > 0, MOD( Dates[MonthOfYear] + 6, 12), 12 ) - Default Sort FiscalQuarterNo. = INT( (Dates[FiscalMonthNo.] + 2 ) / 3 ) - Default Sort Fiscal Quarter = "Q" & Dates[FiscalQuarterNo.] - Sort by FiscalQuarterNo.
This worked like a charm!!! You are a rockstar!!!
Thanx but not really. I actually lifted this code from one of the Summit’s that Sam did awhile back.
I keep it in OneNote and bring it out if I need to look at anything that requires a Fiscal Year.
Happy to have helped.