Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Antonio_Gomez
Resolver I
Resolver I

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.