Calender Table Financial Year Start IN July - July Should be 1st Quarter

Hi All ,

The below date table code, is working fine. Then I want the start of the Financial Year in 1-July Each year, hence when I invoke start with 7 , all seem to be OK except Quarter. If the financial Year starts from July then July should be Qtr 1 ( July-Sept ) , Qtr 2 ( Oct-Dec) , Qtr 3 ( Jan - Mar) , Qtr 4 ( Apr-Jun) .

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))

I manage to do it in dax. Then cant seem to get weekend ending date in Dax.
Please help ammend the table code or help weekend ending date in DAX

Dates = CALENDAR(DATE(2015,1,1),DATE(2016,12,31)) 
MonthNo = MONTH('Dates'[Date]) 
AusMonth = IF('Dates'[MonthNo]>6,'Dates'[MonthNo]-6,'Dates'[MonthNo]+6)  

Then Quater

Quater = INT(('Date'[AusMonth]+2)/3)

This is giving me Qtr1 in July, but then I don’t know how to do weekend in Dax.

Please help to adjust the m.code for me, please .

Are you using Sam’s Template for your Calendar Table setup? Here is video of his setup, and also will attach the JSON file. When you setup the query from this file it asks what month you would like you FY to start with. Let me know if this helps, or you need some other guidance.


Power BI Date Table code.txt (2.8 KB)


Also I recommend doing a quick search on financial quarters and YouTube. You’ll find a tutorial video on how to get quarters set up and the date table.

The solution is in a calculated column, but works fine nonetheless.


To get the weekend I use the following DAX

Weekend - Weekday =
    TRUE (),
    Dates[DayInWeek] = 0, "Weekend",
    Dates[DayInWeek] = 1, "Weekday",
    Dates[DayInWeek] = 2, "Weekday",
    Dates[DayInWeek] = 3, "Weekday",
    Dates[DayInWeek] = 4, "Weekday",
    Dates[DayInWeek] = 5, "Weekday",
    Dates[DayInWeek] = 6, "Weekend",
    BLANK ()