Forum Discussion
Helper IIFinancial Year Calendar
Hi Guy,
I would like to use the below for a Financial Year (April to April). Is there anyway set up the DAX to let the calendar start the 01/04/2019 as Period 1, Q1 and end on 31/03/2019 as Period 12, Q4?
thanks!!
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
Well, it seems you were very close. Important is that the FORMAT( [Date], "M") formula doesn't return a number, so encapsulate it in a VALUE() statement;
Date = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1), DATE(2025,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "FinQ", IF(VALUE(FORMAT([Date], "Q")) = 1, 4, VALUE(FORMAT([Date], "Q"))-1), "FinP", IF(VALUE(FORMAT([Date], "M")) < 4, VALUE(FORMAT([Date], "M")) + 9, VALUE(FORMAT([Date], "M")) - 3))Please mark this reply as Solution if it answered your question 🙂
1 Reply
- JarroVGIT
Resident Rockstar
Well, it seems you were very close. Important is that the FORMAT( [Date], "M") formula doesn't return a number, so encapsulate it in a VALUE() statement;
Date = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1), DATE(2025,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "FinQ", IF(VALUE(FORMAT([Date], "Q")) = 1, 4, VALUE(FORMAT([Date], "Q"))-1), "FinP", IF(VALUE(FORMAT([Date], "M")) < 4, VALUE(FORMAT([Date], "M")) + 9, VALUE(FORMAT([Date], "M")) - 3))Please mark this reply as Solution if it answered your question 🙂