Forum Discussion
Syndicate_Admin
Administrator
AdministratorSemana Fiscal
Hola Tengo una tabla que me da el mes y el año fiscal, pero para el número de la semana fiscal solo tiene un número de índice que continúa creciendo en lugar de reiniciarse cada año. Obtengo esto...
Pruebe el siguiente código:
let // Enter the date in Advanced Editor for the first day of the earliest Fiscal Year in #date(yyyy,m,d) format StartDate = #date(2021, 1, 3), // Enter the desired range of years as List in Advanced Editor YearRange = {2021..2050}, // In Advanced Editor, use one of the two options for this step (code out the one not used with //) to either hard code or dynamically calculate the years with 53 weeks. Substitute a different evaluation criterion as needed. YearsWith53Weeks = {2020}, //YearsWith53Weeks = List.Select(YearRange, each Date.DayOfWeekName(#date(_,11,30)) = "Saturday"), StartingTable = Table.FromColumns({YearRange}, {"Year"}), AddNumberOfWeeksColumn = Table.AddColumn(StartingTable, "Weeks", each if List.Contains(YearsWith53Weeks, [Year]) then 53 else 52), #"Changed Type4" = Table.TransformColumnTypes(AddNumberOfWeeksColumn,{{"Year", Int64.Type}, {"Weeks", Int64.Type}}), // In the Advanced Editor, enter the two patterns for 52 and 53 week years as a list of weeks per fiscal month AddListOfMonthAndWeekCounts = Table.AddColumn(#"Changed Type4", "Custom", each if [Weeks] = 53 then List.Zip({{1..12}, {4,4,5,4,4,5,4,4,5,4,5,5}}) else List.Zip({{1..12}, {4,5,4,4,5,4,4,5,4,4,5,4}})), #"Expanded Custom" = Table.ExpandListColumn(AddListOfMonthAndWeekCounts, "Custom"), #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"FM", "NumWeeks"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FM", Int64.Type}, {"NumWeeks", Int64.Type}}), AddListOfWeeksColumn = Table.AddColumn(#"Changed Type", "WeekInFM", each {1..[NumWeeks]}), #"Expanded Custom1" = Table.ExpandListColumn(AddListOfWeeksColumn, "WeekInFM"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"WeekInFM", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"Year"}, {{"All", each _, type table [Year=nullable number, Weeks=nullable number, FM=nullable number, NumWeeks=nullable number, WeekInFM=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All], "New FW Index", 1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "All"}), #"Expanded Custom2" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Year", "Weeks", "FM", "NumWeeks", "WeekInFM", "New FW Index"}, {"Year", "Weeks", "FM", "NumWeeks", "WeekInFM", "New FW Index"}), AddWeekIndex = Table.AddIndexColumn(#"Expanded Custom2", "FW_Index", 1, 1, Int64.Type), Add7DayListPerWeek = Table.AddColumn(AddWeekIndex, "WeekDay", each {1..7}), #"Expanded WeekDay" = Table.ExpandListColumn(Add7DayListPerWeek, "WeekDay"), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded WeekDay",{{"WeekDay", Int64.Type}}), RemoveUnneededColumns = Table.RemoveColumns(#"Changed Type2",{"Weeks", "NumWeeks", "WeekDay"}), AddDayIndex = Table.AddIndexColumn(RemoveUnneededColumns, "DayIndex", 0, 1, Int64.Type), AddDatesBasedOnStartDateAndDayIndex = Table.AddColumn(AddDayIndex, "Date", each Date.AddDays(StartDate, [DayIndex]), type date) in AddDatesBasedOnStartDateAndDayIndexSi el problema aún no se resuelve, proporcione información detallada sobre el error o el resultado esperado que espera. Hágamelo saber de inmediato, esperando su respuesta.
Saludos
Winniz
Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.
Syndicate_AdminAdministrator
AdministratorUtilizo este código, la solución de la semana fiscal puede ser diferente a la que usas, pero es interesante comparar
dejar
Inicio de las configuraciones
Today = Date.From(DateTime.LocalNow()),
Fecha de hoy
DesdeAño = 2013,
Establezca el año de inicio de la dimensión de fecha. las fechas comienzan a partir del 1 de enero de este año
ToAño = 2033,
Establezca el año final de la dimensión Fecha. las fechas terminan el 31 de diciembre de este año
IniciodeAño Fiscal = 4,
Establezca el número de mes que es el inicio del año financiero. ejemplo; si el inicio del año fiscal es julio, el valor es 7
firstDayofWeek = Día.Lunes,
establecer el día de inicio de la semana, valores: Día.Lunes, Día.Domingo....
Fin de la configuración
FromDate = #date(FromYear, 1, 1),
ToDate = #date(ToYear, 12, 31),
Source = List.Dates(FromDate, Duration.Days(ToDate - FromDate) + 1, #duration(1, 0, 0, 0)),
#"Convertido en tabla" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"columnas renombradas" = Table.RenameColumns(#"Convertido en tabla", {{"Column1", "Fecha"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
FiscalMonthBaseIndex = 13 - StartofFiscalYear,
adjustedFiscalMonthBaseIndex =
if (FiscalMonthBaseIndex >= 12 o FiscalMonthBaseIndex < 0) then
0
más
FiscalMonthBaseIndex,
#"Added Custom" = Table.AddColumn(
#"Changed Type", "FiscalBaseDate", each Date.AddMonths([Date], adjustedFiscalMonthBaseIndex)
),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"FiscalBaseDate", type date}}),
#"Año insertado1" = Table.AddColumn(
#"Changed Type1", "Fiscal Year", each Date.Year([FiscalBaseDate]), Int64.Type
),
#"Insertado Quarter1" = Table.AddColumn(
#"Año insertado1",
"Trimestre fiscal",
cada "FY" & Text.End(Text.From([Año fiscal]), 2) & "Q" & Text.From(Date.QuarterOfYear([FiscalBaseDate]))
),
#"Mes insertado1" = Table.AddColumn(
#"Cuarto insertado1",
"Mes fiscal",
cada
"FY"
& Text.End(Text.From([Año fiscal]), 2)
& "m"
& (Text.PadStart(Text.From(Date.Month([FiscalBaseDate])), 2, "0"))
),
#"Columnas eliminadas" = Table.RemoveColumns(#"Mes insertado1", {"FiscalBaseDate"}),
#"Added Custom4" = Table.AddColumn(#"Columnas eliminadas", "Año-Mes", cada Date.ToText([Date], "yy-MM")),
getISO8601Week = (someDate as date) =>
dejar
getDayOfWeek = (d como fecha) => let result = 1 + Date.DayOfWeek(d, Day.Monday) in result,
getNaiveWeek = (inDate as date) =>
dejar
Lunes = 1, Domingo = 7
weekday = getDayOfWeek(inDate),
weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),
ordinal = Date.DayOfYear(inDate),
naiveWeek = Number.RoundDown((ordinal - día de la semana + 10) / 7)
en
ingenuWeek,
thisYear = Date.Year(someDate),
previoYear = thisYear - 1,
nwn = getNaiveWeek(someDate),
lastWeekOfPriorYear = getNaiveWeek(#date(priorYear, 12, 28)),
lastWeekOfThisYear = getNaiveWeek(#date(thisYear, 12, 28)),
weekYear = if nwn < 1 then priorYear else if nwn > lastWeekOfThisYear then thisYear + 1 else thisYear,
weekNumber = if nwn < 1 then lastWeekOfPriorYear else if nwn > lastWeekOfThisYear then 1 else nwn,
week_dateString = Text.PadStart(Text.From(Number.RoundDown(weekNumber)), 2, "0")
en
Text.End(Text.From(weekYear), 2) & "W" & week_dateString,
Custom1 = Table.AddColumn(#"Added Custom4", "Day Name", each Date.DayOfWeekName([Date]), escriba text),
#"Added custom1" = Table.TransformColumnTypes(
Table.AddColumn(Custom1, "Year-Week", each getISO8601Week([Date])), {{"Year-Week", type text}}
),
#"Added Custom1" = Table.AddColumn(
#"Añadido personalizado1",
"Semana Fiscal",
cada
dejar
fiscalStartWeek = Number.FromText(Text.End(getISO8601Week(#date(Date.Year([Date]), 4, 1)), 2)),
semana = Number.FromText(Text.End([#"Año-Semana"], 2)),
lastWeekOfFiscalYear = Number.FromText(
Text.End(getISO8601Week(#date(Date.Year([Date]) - 1, 12, 28)), 2)
),
lastfiscalStartWeek = Number.FromText(Text.End(getISO8601Week(#date(Date.Year([Date]) - 1, 4, 1)), 2)),
fiWeek =
if [Date] < #date(Date.Year([Date]), 4, 1) then
if week < fiscalStartWeek then
lastWeekOfFiscalYear + semana - lastfiscalStartWeek + 1
else if week = fiscalStartWeek then
if [Date] < #date(Date.Year([Date]), 4, 1)then
lastWeekOfFiscalYear + semana - lastfiscalStartWeek + 1 más
semana - fiscalStartWeek + 1
más
semana - fiscalStartWeek
más
semana - fiscalStartWeek + 1
en
"FY" & Text.End(Text.From([#"Año fiscal"]), 2) & "w" & Text.End(Text.From(fiWeek), 2)
)
en
#"Añadido personalizado1"