Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Creates a Date dim with fiscal calendar all based off of a calculated table. Assumes months are calendar. Fiscal Year start determined by value in [Fiscal Year Month Begin]
{
"name": "Date",
"dataCategory": "Time",
"columns": [
{
"type": "calculatedTableColumn",
"name": "Date",
"dataType": "dateTime",
"isNameInferred": true,
"isDataTypeInferred": true,
"isKey": true,
"sourceColumn": "[Date]",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Month Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "month([Date])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "DateKey",
"dataType": "int64",
"isDataTypeInferred": false,
"expression": "VALUE(FORMAT([Date],\"YYYYMMDD\"))",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Year",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "YEAR([Date])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "YearMonthNum",
"dataType": "string",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "format([Date],\"YYYYMM\")"
},
{
"type": "calculated",
"name": "Month Year Abbrev",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "format([Date],\"mmm YYYY\")",
"sortByColumn": "YearMonthNum"
},
{
"type": "calculated",
"name": "Month Name",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "format([Date],\"mmmm\")",
"sortByColumn": "Month Nbr"
},
{
"type": "calculated",
"name": "Month Abbrev",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "Format([Date],\"mmm\")",
"sortByColumn": "Month Nbr"
},
{
"type": "calculated",
"name": "Day Of Week Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "Weekday([Date])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Day Of Week",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "Format([date],\"dddd\")",
"sortByColumn": "Day Of Week Nbr"
},
{
"type": "calculated",
"name": "Weekday Abbrev",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "left([Day Of Week],3)",
"sortByColumn": "Day Of Week Nbr"
},
{
"type": "calculated",
"name": "Quarter Nbr",
"dataType": "double",
"isDataTypeInferred": true,
"expression": "VALUE(format([date],\"Q\"))",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Quarter",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"Q\" & [Quarter Nbr]",
"sortByColumn": "Quarter Nbr"
},
{
"type": "calculated",
"name": "Quarter Year",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "[Quarter] & \" \" & [Year]",
"sortByColumn": "YrQtrSort"
},
{
"type": "calculated",
"name": "YrQtrSort",
"dataType": "double",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "[year]*10+[Quarter Nbr]"
},
{
"type": "calculated",
"name": "Day of Month",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "day([date])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Fiscal Month Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "month(edate([date],[Month Shift]))",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Fiscal Month",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"FM \" & [Fiscal Month Nbr]",
"sortByColumn": "Fiscal Month Nbr"
},
{
"type": "calculated",
"name": "Fiscal Year Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": " if( [Month Nbr] >= VALUE(format(Date([Year],[Fiscal Year Month Begin],1),\"M\")), [Year]+1, [Year])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Fiscal Year",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"FY \" & [Fiscal Year Nbr]",
"sortByColumn": "Fiscal Year Nbr"
},
{
"type": "calculated",
"name": "FiscalMonthSort",
"dataType": "int64",
"isDataTypeInferred": true,
"isHidden": true,
"expression": " [Fiscal Year Nbr]*100+[Fiscal Month Nbr]"
},
{
"type": "calculated",
"name": "Fiscal Month Year",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "[Month Abbrev] & \" \" & [Fiscal Year Nbr]",
"sortByColumn": "FiscalMonthSort"
},
{
"type": "calculated",
"name": "Fiscal Quarter Nbr",
"dataType": "double",
"isDataTypeInferred": true,
"expression": "ROUNDUP(MONTH(EDATE([Date],[Month Shift]))/3,0)",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Fiscal Quarter",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"Q\" & [Fiscal Quarter Nbr]",
"sortByColumn": "Fiscal Quarter Nbr"
},
{
"type": "calculated",
"name": "Fiscal Quarter Year",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "[Fiscal Quarter] & \" \" & [Fiscal Year Nbr]",
"sortByColumn": "Fiscal Quarter Sort"
},
{
"type": "calculated",
"name": "End Of Month",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "EOMONTH([date],0)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "End Of Quarter",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "if([Month Nbr]<4,date([Year],3,31),if([Month Nbr]<7,DATE([Year],6,30),if([Month Nbr]<10,Date([Year],9,30),Date([Year],12,31))))",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "End of Year",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "date([Year],12,31)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "End of Fiscal Year",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "EOMONTH(Date([Fiscal Year Nbr],[Fiscal Year Month Begin],1),-1)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Fiscal Quarter Sort",
"dataType": "double",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "[year]*10+[Fiscal Quarter Nbr]"
},
{
"type": "calculated",
"name": "Calendar Week Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "WEEKNUM([Date],1)",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Begin of Month",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "date([Year],[Month Nbr],1)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Begin of Quarter",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "if([Month Nbr]<4,date([Year],1,1),if([Month Nbr]<7,DATE([Year],4,1),if([Month Nbr]<10,Date([Year],7,1),Date([Year],10,1))))",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Begin of Year",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "date([Year],1,1)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Begin of Fiscal Quarter",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": [
"if([Fiscal Quarter Nbr]=1,[Begin of Fiscal Year],",
"if([Fiscal Quarter Nbr] = 2, EDATE([Begin of Fiscal Year],3),",
"if([Fiscal Quarter Nbr] = 3, EDATE([Begin of Fiscal Year],6),",
"EDATE([Begin of Fiscal Year],9))))"
],
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Begin of Fiscal Year",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "date([Fiscal Year Nbr]-1,[Fiscal Year Month Begin],1)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Fiscal Week Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "INT(([Date]-Weekday([Date]+1)-Date('Date'[Year]-([Month Nbr]<[Fiscal Year Month Begin]),[Fiscal Year Month Begin],1))/7)+2",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Calendar Week",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"W\"&[Calendar Week Nbr]",
"sortByColumn": "Calendar Week Nbr"
},
{
"type": "calculated",
"name": "Fiscal Week",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"FW\"&[Fiscal Week Nbr]",
"sortByColumn": "Fiscal Week Nbr"
},
{
"type": "calculated",
"name": "Month Shift",
"dataType": "int64",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "13 - [Fiscal Year Month Begin]"
},
{
"type": "calculated",
"name": "Fiscal Year Month Begin",
"dataType": "int64",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "10"
},
{
"type": "calculated",
"name": "MonthDiff",
"dataType": "int64",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "DateDiff([Begin of Fiscal Year],[Date],MONTH)"
},
{
"type": "calculated",
"name": "End of Fiscal Quarter",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": [
"if([Fiscal Quarter Nbr]=1,EDATE([Begin of Fiscal Year],3)-1,",
"if([Fiscal Quarter Nbr] = 2, EDATE([Begin of Fiscal Year],6)-1,",
"if([Fiscal Quarter Nbr] = 3, EDATE([Begin of Fiscal Year],9)-1,",
"EDATE([Begin of Fiscal Year],12)-1)))",
""
],
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
}
],
"partitions": [
{
"name": "CalculatedTable 1",
"source": {
"type": "calculated",
"expression": "Calendar(DATE(2010,1,1),DATE(2025,12,31))"
}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment