Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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.
@bonafides, refer to these
https://www.youtube.com/watch?v=JqVnqMLGWDY
For the wise analysis of the week
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
@bonafides, refer to these
https://www.youtube.com/watch?v=JqVnqMLGWDY
For the wise analysis of the week
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
@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.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |