Weekly data - week 53

[Irina]

Hi.

I am so hoping you can help with this.

The dataset is quite simple, but working with weekly data has been difficult.
I used ISO calendar to get the weeks right. But i still have questions.

I am wondering about the optimal way to organize data in Power Query given that

*i have to filter by country and chose a relevant period (week, year)
*i have to calculate a minimum, maximum, average per week
*week 53 does should appear for min, max, avg even when the selected year has 52 weeks
*one visual should start at week 40 (which is the hydrological year in Norway).
data for test.xlsx (83.8 KB)
Mentoring.pbix (914.0 KB)

I hope to hear soon from you and that you can help.

Attached excel and pbix file.

Thank you in advance.

[BrianJ]

@Irina ,

The immediate problem I see is that your data model is not yet completed. You have the tables, but there are no relationships set between them, so no DAX is going to work.

In terms of the Week 53 display issue, I believe I solved this for you in the following post:

ISO week - week 53

@Irina , Okay, I’ve solved your week 53 display problem. I’ve run into this before myself and here’s how I handled it: created a separate week number table from 1 to 53 and snowflaked that off of the primary date table replaced the week number field in the table and the visual with the week number from the table created above made sure to turn on the “show items with no data” option. Here’s how the data model and the visual now look: [Snag_40ae615] [Snag_40ba58c] I do have a question ab…

In terms of starting with a particular week in a visual, I’ve addressed this in the following video:

How To Dynamically Select Starting Period For Any Chart In Power BI

In terms of calculating min, max and average per week in Power Query, you can use the following technique:

Unleash The Potential of Group By With All Rows

This should give you everything you need to develop your solution, but if you still have questions after going through these, please give a shout.

I hope this is helpful.

• Brian

[Irina]

Thank you @Brian I tried the solutions you advised and

1.Adding a Disconnected table, makes week 53 show for those years with 53 weeks but it does not show for Min, Max, Average - and it should regardless of the year selected.
2. Within range filter. Starting with week 40 solutions does work as long as there is one year in the visual. But it does not work if we compare current year with previous years in the same chart.

I guess my biggest struggle right now is the relationship between fact and dimension table when I don’t have daily date. I have a column for Year, Week and i added WeekEnding date (20/02/2022) or YearMonth (202207). But it does not work.
I used Group by as you suggested. and determined the relationships, added DAX measures, but since the relationship between fact and dimension table is wrong, nothing works.

Hope that when i get the right relationship, i will understand better solution you propose
Mentoring.pbix (921.6 KB)

thank you for bearing with me.

(I have been travelling for a week and then i got the famous virus for another week, so i could not respond earlier, sorry)

[EnterpriseDNA]

Bumping this post for more visibility.

[ankit]

Hi @Irina - Yes for Week over Week analysis or Prior Year analysis, daily data is necessary in Fact table. Is it not possible to add dates in the Fact table.

Thanks
Ankit J

[EnterpriseDNA]

Hello @Irina, just following up if the response from @ankit and @BrianJ help you solve your inquiry?

We’ve noticed that no response has been received from you since a few days ago. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.