cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Semana del Mes Calendario ISO 445

Chicos huy, he pasado muchas horas tratando de escribir una medida dax para agregar una columna con la semana del número del mes. Quiero decir, calendario 445, así que para los primeros siete días mostrar 1, para los próximos siete 2 y así sucesivamente. El mes que viene comienza el mismo calc.

bonafides_0-1598582321480.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

@bonafides - WEEKNUM admite un 21 como segundo parámetro. Es indocumentado en DAX, pero es el mismo que en Excel. Puede usarlo para obtener un número de semana ISO. https://www.pcworld.com/article/3126393/excel-date-and-time-functions-weeknum-isoweeknum-workday-wor...

Si eso no es así, No hay suficiente información para continuar, por favor, primero compruebe si su problema es un problema común enumerado aquí: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Además, consulte este post sobre cómo obtener respuesta a su pregunta rápidamente: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Las partes más importantes son:
1. Datos de muestra como texto, utilice la herramienta de tabla en la barra de edición
2. Salida esperada de los datos de muestra
3. Explicación en palabras de cómo obtener de 1. a 2.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

@bonafides - WEEKNUM admite un 21 como segundo parámetro. Es indocumentado en DAX, pero es el mismo que en Excel. Puede usarlo para obtener un número de semana ISO. https://www.pcworld.com/article/3126393/excel-date-and-time-functions-weeknum-isoweeknum-workday-wor...

Si eso no es así, No hay suficiente información para continuar, por favor, primero compruebe si su problema es un problema común enumerado aquí: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Además, consulte este post sobre cómo obtener respuesta a su pregunta rápidamente: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Las partes más importantes son:
1. Datos de muestra como texto, utilice la herramienta de tabla en la barra de edición
2. Salida esperada de los datos de muestra
3. Explicación en palabras de cómo obtener de 1. a 2.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

let
Source = List.Buffer(CALENDARIO[DATA]),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DATA"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"DATA", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type1", "DaysYear", each Duration.Days( Date.EndOfYear([DATA]) - Date.StartOfYear([DATA]) )),
#"InsertDayOfWeek" = Table.AddColumn(#"Inserted Year", "DayOfWeek", each Date.DayOfWeek([DATA], 1)),
#"InsertAnoISO" = Table.AddColumn(InsertDayOfWeek, "AnoISO", each if [DaysYear] = 364 then
Date.Year(
Date.AddDays(
[DATA],
3 - [DayOfWeek]
)
)
else
Date.Year(
Date.AddDays(
[DATA],
10 - [DayOfWeek]
)
)),

InsertDataRef = Table.AddColumn(InsertAnoISO, "DataRef", each #date([AnoISO], 1, 3)),

#"InsertSemanaISO" = Table.AddColumn(
InsertDataRef,
"SemanaISO",
each Number.IntegerDivide(
Duration.Days( [DATA] - [DataRef] ) + Date.DayOfWeek([DataRef], 0) + 6,
7
),
type number
),

#"InsertSemanaInt" = Table.AddColumn(
InsertSemanaISO,
"SemanaInt",
each [AnoISO] * 100 + [SemanaISO],
type number
),

#"InsertCalendarSemana" = Table.AddColumn(
InsertSemanaInt,
"SemanaNoCalendario",
each Number.ToText([AnoISO]) & Number.ToText([SemanaISO], "-W00")
),
#"Added Index" = Table.AddIndexColumn(InsertCalendarSemana, "Index", 1, 1),
#"Added Custom2" = Table.AddColumn(#"Added Index", "MonthID", each Number.RoundDown([Index]/91) * 3 +
(if Number.Mod([Index],91)=0 then 0
else if Number.Mod([Index],91)<=28 then 1
else if Number.Mod([Index],91)<=56 then 2
else 3)),
#"Added Custom1" = Table.AddColumn(#"Added Custom2", "MonthNumber", each [MonthID] - (Number.RoundUp([MonthID]/12)-1)*12),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "MonthName", each if [MonthNumber] = 1 then "Janeiro" else if [MonthNumber] = 2 then "Fevereiro" else if [MonthNumber] = 3 then "Março" else if [MonthNumber] = 4 then "Abril" else if [MonthNumber] = 5 then "Maio" else if [MonthNumber] = 6 then "Junho" else if [MonthNumber] = 7 then "Julho" else if [MonthNumber] = 8 then "Agosto" else if [MonthNumber] = 9 then "Setembro" else if [MonthNumber] = 10 then "Outubro" else if [MonthNumber] = 11 then "Novembro" else if [MonthNumber] = 12 then "Dezembro" else "Inválido"),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "QuarterID", each Number.RoundDown(([Index]-1)/91)+1),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"DataRef", "DaysYear", "DayOfWeek", "MonthID", "Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"MonthName", type text}, {"SemanaNoCalendario", type text}, {"MonthNumber", Int64.Type}, {"AnoISO", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"AnoISO", "ANOISO"}, {"SemanaISO", "SEMANAISO"}, {"SemanaInt", "SEMANAINT"}, {"SemanaNoCalendario", "SEMANANOCALENDARIO"}, {"MonthNumber", "NUMMÊS"}, {"MonthName", "NOMEMÊS"}}),
#"Coluna Mesclada Inserida" = Table.AddColumn(#"Renamed Columns1", "MÊS/ANO", each Text.Combine({Text.From([NUMMÊS], "pt-BR"), Text.From([ANOISO], "pt-BR")}, "/"), type text),
#"Personalização Adicionada" = Table.AddColumn(#"Coluna Mesclada Inserida", "CLASSIFICA MÊS/ANO", each 100 * [ANOISO] + [NUMMÊS]),
#"Coluna Condicional Adicionada" = Table.AddColumn(#"Personalização Adicionada", "SEMANASNOMÊS", each if [NUMMÊS] = 1 then 4 else if [NUMMÊS] = 2 then 4 else if [NUMMÊS] = 3 then 5 else if [NUMMÊS] = 4 then 4 else if [NUMMÊS] = 5 then 4 else if [NUMMÊS] = 6 then 5 else if [NUMMÊS] = 7 then 4 else if [NUMMÊS] = 8 then 4 else if [NUMMÊS] = 9 then 5 else if [NUMMÊS] = 10 then 4 else if [NUMMÊS] = 11 then 4 else if [NUMMÊS] = 12 then 5 else 0),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Coluna Condicional Adicionada",{{"MÊS/ANO", type text}, {"CLASSIFICA MÊS/ANO", Int64.Type}, {"SEMANASNOMÊS", Int64.Type}})
in
#"Tipo Alterado"

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors