@BI Milgran Granitos, veja agora, por favor.
let
pDataIni = #date(2024,1,1),
pDataFim = #date(2024,12,31),
ListaDatas = List.Dates(pDataIni, Number.From(pDataFim-pDataIni)+1,#duration(1,0,0,0)),
ConverteEmTabela = Table.FromList(ListaDatas, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenomeiaColunaData = Table.RenameColumns(ConverteEmTabela,{{"Column1", "Data"}}),
AlteraTipoData = Table.TransformColumnTypes(RenomeiaColunaData,{{"Data", type date}}),
InsereColunaAno = Table.AddColumn(AlteraTipoData, "Ano", each Date.Year([Data]), Int64.Type),
InsereColunaMesNum = Table.AddColumn(InsereColunaAno, "Mes_Num", each Date.Month([Data]), Int64.Type),
InserveColunaMesNomeCompleto = Table.AddColumn(InsereColunaMesNum, "Mes_Nome_Completo", each Text.Proper(Date.MonthName([Data])), type text),
InsereColunaMesNomeAbreviado = Table.AddColumn(InserveColunaMesNomeCompleto, "Mes_Nome_Abreviado", each Text.Start([Mes_Nome_Completo], 3), type text),
InsereColunaAnoMesNum = Table.AddColumn(InsereColunaMesNomeAbreviado, "AnoMes_Num", each [Ano]*100+[Mes_Num], type number),
InsereColunaTrimestre = Table.AddColumn(InsereColunaAnoMesNum, "Trimestre", each Text.From(Date.QuarterOfYear([Data])) & "º Tri", type text),
InsereColunaSemestre = Table.AddColumn(InsereColunaTrimestre, "Semestre", each Text.From(Number.IntegerDivide([Mes_Num],6)+1) & "º Sem", type text),
InsereColunaDia = Table.AddColumn(InsereColunaSemestre, "Dia", each Date.Day([Data]), Int64.Type),
InsereColunaMesAnoNome = Table.AddColumn(InsereColunaDia, "MesAno_Nome", each Text.Combine({[Mes_Nome_Abreviado], Text.From([Ano], "pt-BR")}, "-"), type text),
InsereColunaDiaSemana = Table.AddColumn(InsereColunaMesAnoNome, "DiaSemana", each Date.DayOfWeek([Data], Day.Saturday), type number),
InsereColunaSemanaMes =
Table.AddColumn(
InsereColunaDiaSemana,
"Semana_Mes",
each
let
vAnoMesNum = [AnoMes_Num],
vData = [Data],
vPrimeiroDiaMes = Date.StartOfMonth(vData),
vUltimoDiaMes = Date.EndOfMonth(vData),
vDiaSemanaPrimeiroDiaMes = Date.DayOfWeek(vPrimeiroDiaMes, Day.Saturday),
vDiaSemanaUltimoDiaMes = Date.DayOfWeek(vUltimoDiaMes, Day.Saturday),
vPrimeiroSabado = Date.AddDays(vPrimeiroDiaMes, if vDiaSemanaPrimeiroDiaMes = 0 then 0 else 7 - vDiaSemanaPrimeiroDiaMes),
vUltimoSabado = Date.AddDays(vUltimoDiaMes, if vDiaSemanaUltimoDiaMes = 0 then 0 else - vDiaSemanaUltimoDiaMes),
vUltimoDiaMesAnterior = Date.AddDays(vPrimeiroDiaMes, -1),
vPrimeiroDiaMesAnterior = Date.StartOfMonth(vUltimoDiaMesAnterior),
vDiaSemanaPrimeiroDiaMesAnterior = Date.DayOfWeek(vPrimeiroDiaMesAnterior, Day.Saturday),
vPrimeiroSabadoMesAnterior = Date.AddDays(vPrimeiroDiaMesAnterior, if vDiaSemanaPrimeiroDiaMesAnterior = 0 then 0 else 7 - vDiaSemanaPrimeiroDiaMesAnterior),
vNumMesAtual = [Mes_Num],
vSemanaMesUltimoDiaMesAnterior = Date.WeekOfMonth(vUltimoDiaMesAnterior, Day.Saturday),
vDiaSemanaMes = Date.WeekOfMonth(vData, Day.Saturday),
vResultado =
if vDiaSemanaPrimeiroDiaMes < 4 then
if vData < vUltimoSabado then
vDiaSemanaMes
else
if vDiaSemanaUltimoDiaMes >= 3 then
vDiaSemanaMes
else
1
else
if vData < vPrimeiroSabado then
vSemanaMesUltimoDiaMesAnterior + (if vDiaSemanaPrimeiroDiaMesAnterior > 3 then -1 else 0)
else
if vData < vUltimoSabado then
vDiaSemanaMes - 1
else
if vDiaSemanaUltimoDiaMes < 4 then
1
else
vDiaSemanaMes - 1
in
vResultado,
type number
)
in
InsereColunaSemanaMes
20240429_SemanaMesIniciandoSabado.pbix