Error in card with weeks

[Antonio_Gomez]

Hi everyone!

I'm having this issue. When I put a range of dates for some dates the text shown in the cards is correct but for others not.

The Power Query Editor is Ok, I have:

• A column "Dia" in which there are the dates (01/01/2021, etc.)
• A column for Week-Year [Semana-Año]: Table.AddColumn(#"Changed Type1", "Semana-Año", each "Semana " & Number.ToText(Date.WeekOfYear([Dia])) & "/" & Number.ToText(Date.Year([Dia])))

So, for every day in the [Semana-Año] column I have the number of the week of the year and the year. For example: for the date 01/01/2021 the text shown in [Semana-Año] is 1/2021, because is the first week of the year and for 01/04/2021 is 2/2021, because is the second week of the year (Just for this example I'm using the american format for dates)

When I select this dates this is the result and it is wrong (Notice that I'm not using the american format for dates). It suposed to show for "Inicio..." Semana 53/2021 and for "Final..." 2/2022

This next image is correct

This other is OK

But if i continue increasing the gap between weeks in some point it is broken again

For the card "Inicio del Rango Seleccionado" I have selected to show the first

For the card "Fin del Rango Seleccionado" I have selected to show the last

I'm putting the column [Semana-Año] directly in the field of the slicer

What is happening? If I want to put something that shows the weeks of the range selected, What should I do?

Pleaseeeee I need help

[Signore_Ands]

Add a format to your query - and "D2" after the Number.ToText for Week:

= Table.AddColumn(#"Changed Type", "Semana-Año", each "Semana " & Number.ToText(Date.WeekOfYear([Found Date]),"D2") & "/" & Number.ToText(Date.Year([Found Date])))

That fixed it for me:

Refer to: Add Leading Zeros to a Number in Power BI Using Power Query - RADACAD

[Antonio_Gomez]

Hello @Signore_Ands 

I'm really glad you are helping me. Now, I understand the adding leading zeros, thanks.

I did it but only works for me if I switch Week/Year to Year/Week when the range is in differents year.

= Table.AddColumn(#"Changed Type1", "Semana-Año", each "Semana " & Number.ToText(Date.Year([Dia])) & "/" & Number.ToText(Date.WeekOfYear([Dia]),"D2"))

 = Table.AddColumn(#"Changed Type1", "Semana-Año", each "Semana " & Number.ToText(Date.WeekOfYear([Dia]),"D2") & "/" & Number.ToText(Date.Year([Dia])) )

I think it's because the order is given only by the week number.

[Signore_Ands · Accepted solution]

Hola @Antonio_Gomez 

You'll need to use a measure then to display the correct First and Last Values. See below.  Hopefuly this solves it finally for you.

I've created two measures:

Semana-Año Primero =

VAR vUltima = MAX(Calendar_Months[calendar_dt])

VAR vPrimero = MIN(Calendar_Months[calendar_dt])

RETURN

"Semana " & FORMAT(WEEKNUM(vPrimero),"0#") & "/" & YEAR(vPrimero)

Semana-Año Ultima =

VAR vUltima = MAX(Calendar_Months[calendar_dt])

VAR vPrimero = MIN(Calendar_Months[calendar_dt])

RETURN

"Semana " & FORMAT(WEEKNUM(vUltima),"0#") & "/" & YEAR(vUltima)

[Antonio_Gomez]

It works, you are the best!

[Signore_Ands]

...because it's Text!

9/2020 is later than 53/2020 (week for 28 Dec) in text terms, because 9 comes after 5!

Your Semana-Año column - could you change that to Año-Semana?
so 202001, 202002 etc?

[Antonio_Gomez]

I didn't notice that!!!!

@Signore_Ands , if I want to show the week/year What should I do?

[Signore_Ands]

Yeah - I can replicate the problem on my data too:

[Signore_Ands]

Hola Antonio

Perhaps this is a long shot - What is the source of your data?  I aks becasue I had a similar issue this past week with dates stored in a Sharepoint list (always in US format although our systems are generally non-US)

I suppose it would also be worth checking what the data type is for the Dia column in Power Query.

[Antonio_Gomez]

Hi @Signore_Ands 

I don't think that's the problem because the column [Semana-Año] is OK in the table "Calendario Base".

Look at the sequence of the pictures the date 03/01/2022 is correct in two pictures but in the last it's wrong.

This last picture it supposed to show "Semana 2/2022" for "Inicio..." and "Semana 10/2022" for "Final..."