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

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
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors