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]

SSASTabDateDim.JSON

  {
        "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))"
            }
          }
        ]
      }