cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

bonafides_0-1598582321480.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
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

Also, check out this post on how to get answers to your question quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.


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

@ 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 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

Also, check out this post on how to get answers to your question quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.


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

@ 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