Missing Quarter in calendar year

[smiller]

I have a calendar set up. 

Where October is my fiscal start: 

Fiscal Month = Month(edate(CalendarTable[Date],-9))

I have a date column

 CalendarTable = CALENDARAUTO(9)

and a quarter and month column

    Fiscal Quarter = "Q" & Quarter(edate(CalendarTable[Date],-9))

                Month = Format(CalendarTable[Date], "mmm")

I releated the fiscal calendar to my data on date to service_date many to 1 cardinality and singe on cross.

I have data for every quarter in 2023 in my source data; however Q1 does not show up on my visualizations, it does for 2022,20201, 2020 etc. I have no filters on accidently. The data columns are formatted the same way as short date. My data comes in via directquery from SQL server.  All the other years are fine? 
Also its grabbing years back to early 1900s? My direct query clearly has >=10-01-2020 for that date column, there are no other date columns in my query. 

[smiller]

Thank you!!!!

[Ashish_Mathur]

Hi,

There is some problem with the Calendar Table.  One will have to work on the file to identify the reason.

[NilR · Accepted solution]

Step One:

Date =
VAR MinYear = YEAR ( MIN ( Sales[Order Date] ) )
VAR MaxYear = YEAR ( MAX ( Sales[Order Date] ) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday number", WEEKDAY( [Date] ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)

Step two:

Sales YTD 2 =
CALCULATE (
    [Sales Amount],
    VAR FirstFiscalMonth = 3 -- Set the first month of the fiscal year
    VAR LastDay =
        MAX ( 'Date'[Date] )
    VAR LastMonth =
        MONTH ( LastDay )
    VAR LastYear =
        YEAR ( LastDay )
            - IF ( LastMonth < FirstFiscalMonth, 1 )
    VAR FilterYtd =
        DATESBETWEEN (
            'Date'[Date],
            DATE ( LastYear, FirstFiscalMonth, 1 ),
            LastDay
        )
    RETURN
        FilterYtd
)

[smiller]

Thanks! Where steps 2 go? New column? New Measure? and what is [Sales_Amount]?

[NilR]

First is your Calendar table and second is your measure to calculate your values in your date range you need. 

[smiller]

But what is [Sales_Amount]?

[NilR]

Any value you have, can be Just sum of your Sale, Count of orders, or Contract, or sum /count of something! Second step depends on what you might need or ignor if you don't. but based on your original question you only need the first step to create your calendar table.

[smiller]

Yeah i dont necessarily need a measure to add anything up , but that calendar does not shift october to my first month of the calendar year?

[NilR]

ADD This column to your calendar!

Fiscal Year =

VAR FirstFiscalMonth = 9

RETURN

IF (

MONTH('Calendar Service'[Date]) >= FirstFiscalMonth,

YEAR('Calendar Service'[Date]) + 1,

YEAR('Calendar Service'[Date])

)