Corrigindo número de semanas no ano

[tiagotas]

Gostaria que o numero de semanas de dados no Power BI, estar da mesma forma que o Excel, como fazer?

Dados no Excel;

No Excel uso a fórmula =NÚMSEMANA(DATA;21), por isso começa os primeiros 3 dias do ano sendo a semana 53 de 2020.

Como considero a mesma formatação no Power Bi?

[AlB · Accepted solution]

@tiagotas

You are right. The above suggestion does not work. See this for a possible deployment:

http://excel-inside.pro/blog/2018/03/06/iso-week-in-power-query-m-language-and-power-bi/

or maybe it would be easier to add a column in DAX, just by

WEEKNUM([Data], 21) 

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

[AlB]

Hi @tiagotas 

Update the step's code:

#"Semana do Ano Inserida" = Table.AddColumn(#"Mês Inserido", "Semana do ano", each let aux_ = Date.WeekOfYear([Data]), res_ = if aux_ <> 1 then aux_-1 else let year_ = Date.Year([Data]) in if Date.DayOfWeek(#date(year_,1,1))<>0 then Date.WeekOfYear(#date(year_-1,12,31)) else 1 in res_, Int64.Type)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[tiagotas]

The day 03/01 is still listed as week 1, and should be week 53.
From the day 04/01 which is week 1.

I'd like to understand the code, I couldn't assimilate.

[FernandoRibeiro]

Tiago,

To change the day 01/03/2021 into week 53, just change the beginning of the week. You can change the beginning of the week in the M code, as follows:

each Date.WeekOfYear ([Data], Day.Monday), Int64.Type),

Thus, Monday will become the beginning of the week.

Inserting a new column, in DAX, is not the most efficient way (although it seems easier). The most efficient would be to use the code provided by the AIB member.