cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Antonio_Gomez
Helper II
Helper II

Error in card with weeks

 

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

Antonio_Gomez_1-1652968062730.png

 

This next image is correct

Antonio_Gomez_5-1652968838229.png

 

This other is OK

Antonio_Gomez_6-1652968988505.png

 

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

Antonio_Gomez_7-1652969472960.png

 

 

 

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

Antonio_Gomez_3-1652968398154.png

 

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

Antonio_Gomez_4-1652968555052.png

 

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

Antonio_Gomez_8-1652971022000.png

 

 

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

 

 

1 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)
 
 
Signore_Ands_1-1653301450682.png

 

View solution in original post

9 REPLIES 9
Signore_Ands
Advocate II
Advocate II

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:

Signore_Ands_0-1652974732938.png

 

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




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"))

 

Antonio_Gomez_2-1653038642968.png

 

 

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

Antonio_Gomez_1-1653038351686.png

 

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

 

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)
 
 
Signore_Ands_1-1653301450682.png

 

It works, you are the best!

Signore_Ands
Advocate II
Advocate II

...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?

 

I didn't notice that!!!!

 

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

Signore_Ands
Advocate II
Advocate II

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

Signore_Ands_0-1652973820503.png

 

Signore_Ands
Advocate II
Advocate II

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.

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..."

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors