cancel
Showing results for
Did you mean:
Frequent Visitor

## Week of the Month ISO Calendar 445

Huy guys, I've spending many hours trying do write a dax measure to add a column with week of the month number. I mean, 445 calendar, so for the first seven days show 1, for next seven 2 and so on. Next month begin the same calc.

2 ACCEPTED SOLUTIONS
Super User IV

@bonafides - WEEKNUM supports a 21 as the second parameter. It is undocumented in DAX, but it is the same as in Excel. You can use it to get an ISO week number. https://www.pcworld.com/article/3126393/excel-date-and-time-functions-weeknum-isoweeknum-workday-wor...

If that is not the case, there is not enough information to continue, please first check if your problem is a common problem listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

The most important parts are:
1. Sample data as text, use the table tool in the edit bar
2. Expected output of sample data
3. Explanation in words how to get from 1. 2.

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

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

Proud to be a Super User!

Super User IV

@bonafides, refer to these

https://www.sqlbi.com/blog/marco/2013/02/19/dax-time-intelligence-for-4-4-5-calendar-iso-calendar-an...

For the wise analysis of the week

Proud to be a Super User!

3 REPLIES 3
Super User IV

@bonafides, refer to these

https://www.sqlbi.com/blog/marco/2013/02/19/dax-time-intelligence-for-4-4-5-calendar-iso-calendar-an...

For the wise analysis of the week

Proud to be a Super User!

Super User IV

@bonafides - WEEKNUM supports a 21 as the second parameter. It is undocumented in DAX, but it is the same as in Excel. You can use it to get an ISO week number. https://www.pcworld.com/article/3126393/excel-date-and-time-functions-weeknum-isoweeknum-workday-wor...

If that is not the case, there is not enough information to continue, please first check if your problem is a common problem listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

The most important parts are:
1. Sample data as text, use the table tool in the edit bar
2. Expected output of sample data
3. Explanation in words how to get from 1. 2.

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

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

Proud to be a Super User!

Frequent Visitor

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(
[DATA],
3 - [DayOfWeek]
)
)
else
Date.Year(
[DATA],
10 - [DayOfWeek]
)
)),

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

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

InsertSemanaISO,
"SemanaInt",
each [AnoISO] * 100 + [SemanaISO],
type number
),

InsertSemanaInt,
"SemanaNoCalendario",
each Number.ToText([AnoISO]) & Number.ToText([SemanaISO], "-W00")
),
(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 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") ,
#"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),
#"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),
in

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!