Forum Discussion
Resolver IError 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
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
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)
9 Replies
- Signore_Ands
Advocate III
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_GomezHi 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..."
- Signore_Ands
Yeah - I can replicate the problem on my data too:
- 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
- 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
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)