Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Problema del año fiscal de la tabla de fechas extendidas

Hola Allí;

Uso en la consulta de energía debajo de la fórmula dax para crear una tabla de fechas. Necesito que mi Mes Fiscal comience en abril. Así que abril será el cuarto1. Pero incluso yo elijo en la selección de parámetros FYStartMonthNum = 4 abril parece trimestre 2 , ¿cómo puedo resolver este problema podría ayudarme pls ? Gracias a i avanzar

cap-1.JPG

cap-2.JPG

let fnDateTable = (StartDate como fecha, EndDate como fecha, FYStartMonthNum opcional como número, días festivos opcionales como lista, WDStartNum opcional como número) como tabla =>
dejar
FYStartMonth = si List.Contains( {1..12}, FYStartMonthNum ) entonces FYStartMonthNum else 1,
StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3)),
WDStart = si List.Contains( {0, 1}, WDStartNum ) entonces WDStartNum else 0,
CurrentDate = Date.From(DateTime.FixedLocalNow()),
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Fuente = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddToday = si EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", cada Date.Year([Date]), número de tipo),
InsertYearOffset = Table.AddColumn(InsertYear, "YearOffset", cada date.year([Date]) - Date.Year(Date.From(CurrentDate)), número de tipo),
InsertCompletedYear = Table.AddColumn(InsertYearOffset, "YearCompleted", cada Date.EndOfYear([Date]) < Date.From(Date.EndOfYear(CurrentDate)), escriba logical),

InsertQuarter = Table.AddColumn(InsertCompletedYear, "QuarterOfYear", cada Date.QuarterOfYear([Date]), número de tipo),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter & Year", cada "Q" y Number.ToText([QuarterOfYear]) & " & amp; Number.ToText([Año]), texto de tipo),
InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", cada [Año] * 10000 + [QuarterOfYear] * 100, número de tipo),
InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "QuarterOffset", cada uno ((4 * Date.Year([Date])) + Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) + Date.QuarterOfYear(Date.From(CurrentDate))), número de tipo),
InsertCompletedQuarter = Table.AddColumn(InsertQuarterOffset, "QuarterCompleted", cada Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), escriba logical),

InsertMonth = Table.AddColumn(InsertCompletedQuarter, "MonthOfYear", cada date.month([Date]), número de tipo),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", cada Date.Day([Date]), número de tipo),
InsertMonthName = Table.AddColumn(InsertDay, "Month Name", cada Text.Proper( Date.ToText([Date], "MMMM")), texto de tipo),
InsertMonthShort = Table.AddColumn( InsertMonthName, "MonthShortName", cada uno prueba Text.Proper( Text.Start([Month Name], 3 )) de lo contrario Text.Proper( [Month Name] ), escriba text),
InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", cada text.proper(Text.Start([Month Name], 1)) y Text.Repeat( Character.FromNumber(8203), [MonthOfYear] ), texto de tipo),
InsertCalendarMonth = Table.AddColumn(InsertMonthInitial, "Month & Year", cada uno [MonthShortName] & " " & Number.ToText([Year]), texto de tipo),
InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", cada [Año] * 10000 + [MonthOfYear] * 100, número de tipo),
InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "MonthOffset", cada uno ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) + Date.Month(Date.From(CurrentDate))), número de tipo),
InsertCompletedMonth = Table.AddColumn(InsertMonthOffset, "MonthCompleted", cada Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(CurrentDate)), escriba logical),
InsertMonthEnding = Table.AddColumn(InsertCompletedMonth, "MonthEnding", cada Date.EndOfMonth([Fecha]), fecha de tipo),

InsertDayInt = Table.AddColumn(InsertMonthEnding, "DateInt", cada [Año] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], número de tipo),
InsertDayOfYear = Table.AddColumn(InsertDayInt, "Día del Año", cada Date.DayOfYear([Fecha]), Int64.Type),
InsertDayWeek = Table.AddColumn(InsertDayOfYear, "DayOfWeek", cada Date.DayOfWeek([Fecha]) + WDStart, Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", cada Text.Proper( Date.ToText([Date], "dddd" )), texto de tipo),
InsertDayInitial = Table.AddColumn(InsertDayName, "Weekday Initial", cada Text.Proper(Text.Start([DayOfWeekName], 1)) y Text.Repeat( Character.FromNumber(8203), [DayOfWeek] ), texto de tipo),

InsertWeekNumber= Table.AddColumn(InsertDayInitial, "ISO Weeknumber", cada uno
si Number.RoundDown((Date.DayOfYear([Fecha])-(Date.DayOfWeek([Fecha], Day.Monday)+1)+10)/7)=0
entonces 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)
si (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Fecha], Day.Monday)+1)+10)/7)=53 y (Date.DayOfWeek(#date(Date.Year([Fecha]),12,31), Day.Monday)+1<4))
a continuación, 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), número de tipo),
InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", cada Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )), Int64.Type),
BufferTable = Table.Buffer(Table.Distinct( InsertISOyear[[Iso Year], [DateInt]])),
InsertISOqNum = Table.AddColumn(InsertISOyear, "ISO QuarterOfYear", cada uno si [ISO Weeknumber] >39 a continuación, 4 else if [ISO Weeknumber] >26 then 3 else if [ISO Weeknumber] >13 then 2 else 1, Int64.Type),
InsertISOqtr = Table.AddColumn(InsertISOqNum, "ISO Quarter", cada "Q" y Number.ToText([ISO QuarterOfYear]), texto de tipo),
InsertISOQuarter = Table.AddColumn(InsertISOqtr, "ISO Quarter & Year", cada "Q" & Number.ToText([ISO QuarterOfYear]) & " " & Number.ToText([ISO Year]), texto de tipo),
InsertISOqNy = Table.AddColumn(InsertISOQuarter, "ISO QuarternYear", cada uno [Iso Year] * 10000 + [ISO QuarterOfYear] * 100, número de tipo),
InsertISOday = Table.AddColumn(InsertISOqNy, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] e IT[ISO Year] = OT[ISO Year])), Int64.Type),
InsertCalendarWk = Table.AddColumn(InsertISOqNy, "Week & Year", cada texto.From([ISO Year]) y "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), texto de tipo),
InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", cada uno [Iso Year] * 10000 + [ISO Weeknumber] * 100, Int64.Type),
InsertWeekOffset = Table.AddColumn(InsertWeeknYear, "WeekOffset", cada uno (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, número de tipo),
InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", cada Date.EndOfWeek( [Date], Day.Monday) < Date.From(Date.EndOfWeek(CurrentDate, Day.Monday)), escriba logical),
InsertWeekEnding = Table.AddColumn(InsertCompletedWeek, "WeekEnding", cada Date.EndOfWeek( [Fecha], Day.Monday), fecha de tipo),

AddFY = Table.AddColumn(InsertWeekEnding, "Fiscal Year", cada "FY" y (si [MonthOfYear] >= FYStartMonth then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), escriba text),
AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", cada "FQ" y Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), escriba text),
AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", cada uno (si [MonthOfYear] >= FYStartMonth entonces [Año] +1 else [Año]) * 10000 + Number.RoundUp( Date.Month( Date.AddMonths( [Fecha], - (FYStartMonth -1) )) / 3 ) * 100, número de tipo),
AddFM = Table.AddColumn(AddFQnYr, "Período fiscal", cada uno si [MonthOfYear] >= FYStartMonth entonces [MonthOfYear] - (FYStartMonth-1) else [MonthOfYear] + (12-FYStartMonth+1), escriba text),
AddFMnYr = Table.AddColumn(AddFM , "FPeriodnYear", cada uno (si [MonthOfYear] >= FYStartMonth entonces [Año] +1 más [Año]) * 10000 + [Período Fiscal] * 100, número de tipo),
FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", cada uno si Date.Month([Date]) < FYStartMonth entonces #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
AddFYDateRange = Table.Buffer( Table.ExpandTableColumn( Table.ExpandTableColumn( Table.AddColumn( Table.Group( Table.Group( Table.AddColumn( Table.AddColumn(
Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( { Number.From(FYCalendarStart).. Number.From(EndDate) }, Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "Date"}}),
"FiscalFirstDay", cada uno si Date.Month([Date]) < FYStartMonth entonces #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
"FWStartDate", cada Date.AddYears(Date.StartOfWeek( [Fecha], Day.Monday), 1)),
{"FiscalFirstDay", "FWStartDate"}, {{"AllRows", cada _, tabla de tipos [Date=nullable date, FiscalFirstDay=date, FWStartDate=date]}}),
{"FiscalFirstDay"}, {{"AllRows2", cada _, tabla de tipos [FiscalFirstDay=date, FWStartDate=date, AllRows=table]}}),
"Custom", cada Table.AddIndexColumn( [AllRows2], "FY Week", 1, 1))[[Custom]],
"Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}),
"AllRows", {"Date"}, {"Date"})[[Fecha], [Fy Week]]
),
MergeFYW = Table.NestedJoin(InsertFFD, {"Date"}, AddFYDateRange, {"Date"}, "AddFYWeek", JoinKind.LeftOuter),
ExpandFYWeek = Table.TransformColumnTypes( Table.ExpandTableColumn(MergeFYW, "AddFYWeek", {"FY Week"}, {"Fiscal Week"}),{{"Fiscal Week", Int64.Type}}),
AddFYW = Table.AddColumn( ExpandFYWeek, "Año fiscal & Semana", cada uno si FYStartMonth =1 entonces [#"Semana & Año"] si Date.Month([Fecha]) < FYStartMonth luego Text.From( Date.Year([Date])) & "-" & Text.PadStart( Text.From([Fiscal Week]), 2, "0") else Text.From( Date.Year([Date])+1) & "-" & Text.PadStart(Text.From([Semana Fiscal]), 2, "0"), escriba text),
InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", cada uno si FYStartMonth =1 entonces [WeeknYear] else (if Date.Month([Date]) < FYStartMonth then Date.Year([Date]) else Date.Year([Date])+1) * 10000 + [Semana Fiscal] * 100, Int64.Type),

InsertIsAfterToday = Table.AddColumn(InsertFWeeknYear, "IsAfterToday", cada uno no ([Date] <= Date.From(CurrentDate)), escriba logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", cada uno if Date.DayOfWeek([Date], Day.Monday) > 4 then false else true, type logical),
InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", cada uno si Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),
InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", cada uno si [IsWorkingDay] = true y [IsHoliday] <> true then true else false, type logical),
InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", cada uno si [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text),

CurrentDateRecord = Table.SelectRows(InsertDayType, cada uno ([Fecha] = CurrentDate)),
CurrentISOyear = CurrentDateRecord{0}[ISO Year],
CurrentISOqtr = CurrentDateRecord{0}[ISO QuarterOfYear],
CurrentYear = CurrentDateRecord{0}[Año],
CurrentMonth = CurrentDateRecord{0}[MonthOfYear],
CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
CurrentFW = CurrentDateRecord{0}[FWeeknYear],
InsertISOQtrOffset = Table.AddColumn(InsertDayType, "ISO QuarterOffset", cada uno ((4 * [ISO Year]) + [ISO QuarterOfYear]) - ((4 * CurrentISOyear) + CurrentISOqtr), número de tipo),
InsertISOYrOffset = Table.AddColumn(InsertISOQtrOffset, "ISO YearOffset", cada uno [ISO Year] - CurrentISOyear, type number),
InsertFYoffset = Table.AddColumn(InsertISOYrOffset, "FiscalYearOffset", cada entento (si [MonthOfYear] >= FYStartMonth entonces [Año]+1 más [Año]) - (si CurrentMonth >= FYStartMonth entonces CurrentYear+1 else CurrentYear) de lo contrario null, número de tipo),
InsertCurrentFQ = Table.AddColumn(InsertFYoffset, "IsCurrentFQ", cada uno si [FQuarternYear] = CurrentFQ entonces true else false, type logical),
InsertCurrentFP = Table.AddColumn(InsertCurrentFQ, "IsCurrentFP", cada uno si [FPeriodnYear] = CurrentFP entonces true else false, type logical),
InsertCurrentFW = Table.AddColumn(InsertCurrentFP, "IsCurrentFW", cada uno si [FWeeknYear] = CurrentFW entonces true else false, type logical),
InsertPYTD = Table.AddColumn(InsertCurrentFW, "IsPYTD", cada uno si CurrentYear-1 = [Año] y [Día del Año] <= CurrentDateRecord{0}[Día del Año] entonces true else false, type logical),
ListPrevFYDates = List.Buffer( Table.SelectRows( Table.ExpandTableColumn( Table.NestedJoen(
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( PrevFiscalFirstDay, Number.From(CurrentFiscalFirstDay-PrevFiscalFirstDay),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1), {"Index"},
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( Date.AddYears( PrevFiscalFirstDay, -1), Number.From( PrevFiscalFirstDay - Date.AddYears( PrevFiscalFirstDay, -1)),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1)
, {"Index"}, "Table", JoinKind.LeftOuter), "Table", {"DateFY"}, {"PrevDateFY"}), cada [DateFY] <= CurrentDate)[PrevDateFY] ),
InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", cada uno si [FiscalYearOffset] = -1 y List.Contains(ListPrevFYDates, [Date] ) entonces true else false, type logical),

RemoveToday = Table.RemoveColumns( if EndDate < CurrentDate then Table.SelectRows(InsertPFYTD, cada uno ([Date] <> CurrentDate)) else InsertPFYTD, {"Day of Year", "FiscalFirstDay"}),
ChType = Table.TransformColumnTypes(RemoveToday,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"Período fiscal", Int64.Type}, {"WeekOffset", Int64.Type}, {""MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}}),
ReorderColumns = Table.ReorderColumns(ChType, {"Date", "Year", "YearOffset", "YearCompleted", "QuarterOfYear", "Quarter & Year", "QuarternYear", "QuarterOffset", "QuarterCompleted", "MonthOfYear", "DayOfMonth", "Month Name", "MonthShortName", "Month Initial", "Month & Year", "MonthnYear", "MonthOffset", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Weekday Initial", "Day Type", "ISO Year", "ISO YearOffset", "ISO QuarterOfYear", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO QuarterOffset", "ISO Weeknumber", "Week & Year", "WeeknYear", "WeekOffset", "WeekCompleted", "WeekEnding", "Fiscal Year" , "FiscalYearOffset", "Fiscal Quarter", "FQuarternYear", "IsCurrentFQ", "Fiscal Period", "FPeriodnYear", "IsCurrentFP", "Fiscal Week", "Fiscal Year & Semana", "FWeeknYear", "IsCurrentFW", "IsAfterToday", "IsWorkingDay", "IsHoliday", "IsBusinessDay", "IsPYTD", "IsPFYTD"}, MissingField.Usenull)
in
ReorderColumns, documentación = [
Documentation.Name = " fxCalendar",
Documentation.Description = "Función de tabla de fechas para crear un calendario ISO-8601",
Documentation.LongDescription = "Función de tabla de fechas para crear un calendario ISO-8601",
Documentation.Category = " Tabla",
Documentation.Version = " 1.25: Añadidos cuartos ISO y desplazamientos",
Documentation.Source = " local",
Documentation.Author = " Melissa de Korte ",
Documentation.Examples = { [Description = " Ver: https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390",
Código = " Parámetros opcionales: #(si)
(FYStartMonthNum) Número de mes que comienza el año fiscal, Januari si se omite #(lf)
(Vacaciones) Seleccione una consulta (y columna) que contenga una lista de fechas de vacaciones #(lf)
(WDStartNum) Cambie la numeración predeterminada entre semana de 0-6 a 1-7 introduciendo un 1 #(lf)
#(lf)
Importante tener en cuenta: #(lf)
[Semana Fiscal] comienza un lunes y puede contener menos de 7 días en una primera y/o última semana de un año fiscal #(lf)
[IsWorkingDay] no tiene en cuenta las fechas de vacaciones #(lf)
[IsBusinessDay] tiene en cuenta las fechas navideñas opcionales #(lf)
[IsPYTD] e [IsPFYTD] comparan el anterior [Día del Año] con el número actual [Día del Año], por lo que las fechas no se alinean en años bisiestos",
Resultado = " " ] }
]
in
Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), documentation))

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors