Add Fiscal Year + Fiscal Month to Date table Query

Hi everyone,

I was wondering how you could add the Fiscal Year month to the Date table in the Query editor. I already managed to replace your “AddFY” bit with a working formula for “AddFiscalMonth”, however then I am not able to add both columns (AddFY and AddFiscalMonth).

The below is my current M code that shows the fiscal month instead of fiscal year.
Could someone please advise how I can add both in a table?

Many thanks.

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    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),
    
    AddFiscalMonth = Table.AddColumn(ChangedType1, "FiscalMonth", each if [MonthOfYear] >= FYStartMonth then Text.From(Number.From([MonthOfYear] - 6)) else Text.From(Number.From([MonthOfYear] + 6)) ),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then [Year] else Text.From(Number.From([Year])-1)))

in
    AddFiscalMonth
in
    fnDateTable

Here is a better version of some M code to use when setting up a Date Table. This should give you the columns you need.

Thanks
Jarrett

Power BI Date Table Code with Offsets - Jarrett Version.txt (4.9 KB)

Here is a link to some Microsoft documentation on M Code
https://docs.microsoft.com/en-us/powerquery-m/date-functions

You can also check out this code. Add this to your Power BI using creating a table feature as this is DAX syntax:

Date = VAR FiscalStart = "06"
VAR STARTYEAR = "2015"
VAR ENDYEAR = "2020"
RETURN 
ADDCOLUMNS(
  CALENDAR(Date(STARTYEAR,01,01),(Date(ENDYEAR,12,31))), 
"Year" , YEAR([Date]),
"Short Year" , VALUE(Right(YEAR([Date]),2)),
"Month Number" , Month([Date]),
"Month Number Full" , FORMAT([Date],"MM"),
"Month Full" , FORMAT([Date],"MMMM"),
"Month Abbr" , FORMAT ([Date],"MMM"),
"Week Number", WEEKNUM([Date]),
"Week Number Full" , Format(WEEKNUM([Date]),"00"),
"Day of Month" , DAY([Date]),
"Day of Month Full" , FORMAT(Day([Date]),"00"),
"Day of Week" , WEEKDAY([Date]),
"Day of Week Full" , FORMAT([Date],"dddd"),
"Day of Week Abbr" , FORMAT([Date],"ddd"),
"ISO Date" , YEAR([Date])&FORMAT(Day([Date]),"00")&FORMAT(Day([Date]),"00"),
"Full Date" , DAY([Date])&" "&FORMAT([Date],"MMMM")&" "&YEAR([Date]),
"Quarter Full" , "Quarter " & ROUNDDOWN(Month([Date])/4,0)+1,
"Quarter Abbr" , "Qtr " & ROUNDDOWN(Month([Date])/4,0)+1,
"Quarter" , "Q" & ROUNDDOWN(Month([Date])/4,0)+1,
"Quarter Number" , Format(ROUNDDOWN(Month([Date])/4,0)+1,"00"),
"Quarter and Year" , "Q" & ROUNDDOWN(Month([Date])/4,0)+1 & " "& YEAR([Date]),
"Month and Year Abbr" ,  FORMAT ([Date],"MMM") & " " & YEAR([Date]),
"Quarter and Year Number" , YEAR([Date])&Format(ROUNDDOWN(Month([Date])/4,0)+1,"00"),
"Year and Week", VALUE(YEAR([Date])&FORMAT(WEEKNUM([Date]),00)),
"Year and Month Number", VALUE((YEAR([Date])&FORMAT([Date],"MM"))),
"IS Work Day" , IF(OR(WEEKDAY([Date])=1, WEEKDAY([Date])=7),"NO","YES"),
"Current Month", IF(DATEDIFF(TODAY(),[Date],MONTH)=0,"Yes","No"),
"Current Year", IF(DATEDIFF(TODAY(),[Date],Year)=0,"Yes", "No"),
"Month Offset", (DATEDIFF(TODAY(),[Date],MONTH)),
"Day Offset", (DATEDIFF(TODAY(),[Date],DAY)),
"Fiscal Year", IF(FiscalStart <= FORMAT([Date],"MM"), "FY"&(YEAR([Date])+1),"FY"&YEAR([Date])),
"Fiscal Month", Value(IF(FiscalStart <= FORMAT([Date],"MM"), (Month([Date]) -FiscalStart+1), ((12-FiscalStart+1)+Month([Date])))),
"Fiscal Quarter", If (Value(IF(FiscalStart <= FORMAT([Date],"MM"), (Month([Date]) -FiscalStart+1), ((12-FiscalStart+1)+Month([Date])))) < 4,"Q1", 
    If (Value(IF(FiscalStart <= FORMAT([Date],"MM"), (Month([Date]) -FiscalStart+1), ((12-FiscalStart+1)+Month([Date])))) < 7,"Q2",
        If (Value(IF(FiscalStart <= FORMAT([Date],"MM"), (Month([Date]) -FiscalStart+1), ((12-FiscalStart+1)+Month([Date])))) < 10,"Q3","Q4")))
)

I’ve used this in the past and it’s very effective.

Hi @dillenbram,

I recently updated (my extended version of) the date table query and this includes FQ and FP.
So I hope you find this is helpful.

**UPDATE: For the most up to date version of the Date table M function, please refer to:

**UPDATE: added a ChangedType step at the end

   let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number, optional Holidays as list ) as table =>
      let
    FYStartMonth = try if List.Contains( {1..12}, FYStartMonth ) then FYStartMonth else 1 otherwise 1,
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    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 number),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type number),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
    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]), type text),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date]), Int64.Type),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertMonthEnding = Table.AddColumn(InsertWeekEnding, "MonthEnding", each Date.EndOfMonth([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertMonthEnding, "Week Number", each //Date.WeekOfYear([Date]), type number),
      if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
      then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
      else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
      then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber, "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
    AddFY = Table.AddColumn(InsertQuarternYear, "Fiscal Year", each "FY"&(if FYStartMonth =1 then Text.End(Text.From([Year]), 2) else if [MonthOfYear]>=FYStartMonth then Text.From(Number.From(Text.End(Text.From([Year]), 2))+1) else Text.End(Text.From([Year]), 2)), type text),
    AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
    AddFM = Table.AddColumn(AddFQ, "Fiscal Period", each if [MonthOfYear]>=FYStartMonth then [MonthOfYear]-(FYStartMonth-1) else [MonthOfYear]+(12-FYStartMonth+1), type text),

    InsertIsAfterToday = Table.AddColumn(AddFM, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
    InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if [DayInWeek] = 5 then false else if [DayInWeek] = 6 then false else true, type logical),
    InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),

    InsertWeekOffset = Table.AddColumn(InsertIsHoliday, "WeekOffset", each (Number.From(Date.StartOfWeek([Date]))-Number.From(Date.StartOfWeek(DateTime.LocalNow())))/7, type number),
    InsertMonthOffset = Table.AddColumn(InsertWeekOffset, "MonthOffset", each ((12 * Date.Year([Date])) +  Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) +  Date.Month(Date.From(CurrentDate))), type number),
    InsertQuarterOffset = Table.AddColumn(InsertMonthOffset, "QuarterOffset", each ((4 * Date.Year([Date])) +  Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) +  Date.QuarterOfYear(Date.From(CurrentDate))), type number),
    InsertYearOffset = Table.AddColumn(InsertQuarterOffset, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),

    IdentifyCurrentDate = Table.SelectRows(InsertYearOffset, each ([Date] = CurrentDate)),
    CurrentFiscalYear = IdentifyCurrentDate{0}[Fiscal Year], 
    InsertFYoffset = Table.AddColumn(InsertYearOffset, "FiscalYearOffset", each Number.From(Text.Range([Fiscal Year],2,2)) - Number.From(Text.Range(CurrentFiscalYear,2,2)), type number),

    InsertCompletedWeek = Table.AddColumn(InsertFYoffset, "CompletedWeek", each [WeekEnding] < Date.From(Date.EndOfWeek(CurrentDate)), type logical),
    InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "CompletedMonth", each [MonthEnding] < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
    InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "CompletedQuarter", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),
    InsertChangedType = Table.TransformColumnTypes(InsertCompletedQuarter,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayInWeek", Int64.Type}, {"Week Number", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}})

    in
    InsertChangedType

    in
    fnDateTable
2 Likes

Melissa,

Had to change a few things in you code to get it to work properly on my end.

InsertIsHoliday was bringing back error, just deleted it.
CurrentFiscalYear- Had to adjust FY to Fiscal Year
InsertFYOffset- Had to change FY to Fiscal Year

** I’ve attached what my M code looks like after these changes. I also added CurrentMonth and Day Of Year columns.**

Power BI Date Table Code with Offsets - 4-6-2020.txt (5.6 KB)

Thanks
Jarrett

Thanks Jarrett!

Renamed that in the M code I provided above as well.

.

If you use the M code above as a table query not a function, you can pass a null to the Holiday variable and need to rename the FYStartMonth variable for example like below. Then it should be sorted.

    StartDate = #date( 2015, 1, 1),
    EndDate = #date( 2025, 12, 31),
    FYStartMonthNo = 7,
    Holidays = null,  // Return a List from a Table examples: QueryName[ColumnName] OR  #"Query Name"[ColumnName] OR #" Query Name"[Column Name]

    // Calendar function starts here
    FYStartMonth = try if List.Contains( {1..12}, FYStartMonthNo ) then FYStartMonthNo else 1 otherwise 1,
1 Like

Melissa,

Just out of curiosity, have you ever added a PYTD column in your M code? I usually create a calculated column after the fact, in my Date Table that returns TRUE or FALSE. I know how to accomplish this in DAX, but not in M code.

Thanks
Jarrett

Can you share your DAX? So I have a clear understanding of your requirement.

I always use Sam’s Date code for my calendar an add the following code for the fiscal years:

Short Month = LEFT ( Dates[MonthName] , 3 ) - Sort by MonthOfYear

Quarter = LEFT ( Dates[Quarter & Year] , 2 ) - Sort by QuarterOfYear

Fiscal Year = IF( Dates[MonthOfYear] <= 6, Dates[Year], Dates[Year] + 1 ) - Default Sort

FiscalMonthNo. = IF( MOD( Dates[MonthOfYear] + 6, 12 ) > 0, MOD( Dates[MonthOfYear] + 6, 12), 12 ) - Default Sort

FiscalQuarterNo. = INT( (Dates[FiscalMonthNo.] + 2 ) / 3 ) - Default Sort

Fiscal Quarter = “Q” & Dates[FiscalQuarterNo.] - Sort by FiscalQuarterNo.

Guy

I create a measure first called “Todays Day Of Year”
Todays Day Of Year = CALCULATE( MAX( 'Date'[Day of Year] ), 'Date'[Date]=TODAY())

Then I use this as a calculated column for PYTD=
PYTD = IF(YEAR(TODAY()) -1=[Year] && [Day of Year] <=[ Todays Day Of Year], TRUE, FALSE)

Thanks
Jarrett

@ojones

Can you give this a go Jarrett and let me know what you find, thanks!

** UPDATE: added a ChangedType step at the end

let
    // Enter your variables here
    StartDate = #date( 2019, 1, 1),
    EndDate = #date( 2022, 12, 31),
    FYStartMonthNo = 7,
    Holidays = null,

    // Calendar function starts here
    FYStartMonth = try if List.Contains( {1..12}, FYStartMonthNo ) then FYStartMonthNo else 1 otherwise 1,
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    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 number),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type number),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
    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]), type text),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
    InsertedDayOfYear = Table.AddColumn(InsertCalendarQtr, "Day of Year", each Date.DayOfYear([Date]), type number),
    InsertDayWeek = Table.AddColumn(InsertedDayOfYear, "DayInWeek", each Date.DayOfWeek([Date]), type number),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertMonthEnding = Table.AddColumn(InsertWeekEnding, "MonthEnding", each Date.EndOfMonth([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertMonthEnding, "Week Number", each //Date.WeekOfYear([Date]), type number),
      if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
      then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
      else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
      then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber, "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
    AddFY = Table.AddColumn(InsertQuarternYear, "Fiscal Year", each "FY"&(if FYStartMonth =1 then Text.End(Text.From([Year]), 2) else if [MonthOfYear]>=FYStartMonth then Text.From(Number.From(Text.End(Text.From([Year]), 2))+1) else Text.End(Text.From([Year]), 2)), type text),
    AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
    AddFM = Table.AddColumn(AddFQ, "Fiscal Period", each if [MonthOfYear]>=FYStartMonth then [MonthOfYear]-(FYStartMonth-1) else [MonthOfYear]+(12-FYStartMonth+1), type text),

    InsertIsAfterToday = Table.AddColumn(AddFM, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
    InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if [DayInWeek] = 5 then false else if [DayInWeek] = 6 then false else true, type logical),
    InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),

    InsertWeekOffset = Table.AddColumn(InsertIsHoliday, "WeekOffset", each (Number.From(Date.StartOfWeek([Date]))-Number.From(Date.StartOfWeek(CurrentDate)))/7, type number),
    InsertMonthOffset = Table.AddColumn(InsertWeekOffset, "MonthOffset", each ((12 * Date.Year([Date])) +  Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) +  Date.Month(Date.From(CurrentDate))), type number),
    InsertQuarterOffset = Table.AddColumn(InsertMonthOffset, "QuarterOffset", each ((4 * Date.Year([Date])) +  Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) +  Date.QuarterOfYear(Date.From(CurrentDate))), type number),
    InsertYearOffset = Table.AddColumn(InsertQuarterOffset, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),
    
    IdentifyCurrentDate = Table.SelectRows(InsertYearOffset, each ([Date] = CurrentDate)),
    CurrentFiscalYear = IdentifyCurrentDate{0}[Fiscal Year], 
    CurrentDayOfYear = IdentifyCurrentDate{0}[Day of Year],
    CurrentYear = IdentifyCurrentDate{0}[Year],
    InsertFYoffset = Table.AddColumn(InsertYearOffset, "FiscalYearOffset", each Number.From(Text.Range([Fiscal Year],2,2)) - Number.From(Text.Range(CurrentFiscalYear,2,2)), type number),
    InsertPYTD = Table.AddColumn(InsertFYoffset, "PYTD", each if CurrentYear-1 = [Year] and [Day of Year] <= CurrentDayOfYear then true else false, type logical),

    InsertCompletedWeek = Table.AddColumn(InsertPYTD, "CompletedWeek", each [WeekEnding] < Date.From(Date.EndOfWeek(CurrentDate)), type logical),
    InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "CompletedMonth", each [MonthEnding] < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
    InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "CompletedQuarter", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),
    InsertCurrentMonth = Table.AddColumn(InsertCompletedQuarter,"Current Month", each if Date.IsInCurrentMonth([Date]) then true else false, type logical),
    InsertChangeType = Table.TransformColumnTypes(InsertCurrentMonth,{{"FiscalYearOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"WeekOffset", Int64.Type}, {"Fiscal Period", Int64.Type}, {"QuarternYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"Week Number", Int64.Type}, {"DayInWeek", Int64.Type}, {"Day of Year", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"Year", Int64.Type}})

in
    InsertChangeType
1 Like

Melissa,

Been testing this out, and it almost works perfectly. Only issue I’m running into is this year is a leap year, so in the PYTD column 4/7/19 should say TRUE, not FALSE. It is showing FALSE in your example.

Thanks
Jarrett

Give it another go Jarrett - thanks

Melissa,

Figured it out. Somehow I accidently deleted one of the “=” signs in the PYTD column. Now I’m get this error sign when Closing and Applying the changes. I have attached the PBIX.

image

Thanks
Jarrett

EDNA- PYTD Date Table Query.pbix (174.5 KB)

This is odd, if you view errors that table is empty - but I did solve it though. :wink:
What I’ve done is change every Int64.Type (Whole Number) back into a number and then adding a ChangeType step at the end. Also updated the M codes above for future reference.

EDNA- PYTD Date Table Query.pbix (221.4 KB)

Melissa,

Thanks again for all of your help. :sunglasses:

Jarrett

Hi @dillenbram , we’ve noticed that no response has been received from you since the 6th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi, Thank you for this. please clarify , If I use the updated Date table, does it mean that when I look at Sales by Fiscal Month, it will give me say April to March, rather than January to December?

Hi @Ikay,

That depends on how you declare the FYStartMonth parameter, what other filter context is coming in but more importantly what attributes you’ve selected from the Date table…

Below I’ve set the FYStartMonth as April

.

Just grab the M code from here and explore for a bit. :wink:

I hope this is helpful.

1 Like

I really really appreciate the quick response. Thank you Melissa