Calculate totals, pr week without a date in table

DAX DAX Calculations
1

Revenue-Budget.xlsx (25.9 KB)
In the Attached Excel sheet i have a table With Year, Week, Type (Revenue or Budget) and Value.

There is no date Field in the table, only weeks and year, and so far no

Is it to add a column and calculate date for the last day in the week, and Connect this date Field to the date table ?

I like to calculate totals of Budget and Revenue for each year, quater, month, week, last week, compare it With last year.

DateTable. Any idea how to handle this scenario, With an example ?

Thank you.

Kr

Truls

2

Hi there, just go to query editor, click:
Home—New Source—Blank query—Advance Editor. Clear the canvas and copy and paste the codes which I uploaded.

When you have the date table ready, filter the DayOfWeekName column only select Sunday—the last day of the week. Then you can build relationship between the date table and your fact table and do time intelligence calculations.

Power BI Date Table code.txt (2.8 KB)

3

Thank you !
Can you please geive me a hint about the relationship between date table and the other table. I’m not sure i understand how this actual relationships should be set.

Revenue-budget.pbix|attachment](upload://idAyVbTIbsccTBAVU4IkKhGm3ZT.pbix) (120.5 KB)

4

Hi I can’t download the file you upload.
In my view you can add a merged column(Year & Week Number) in both tables and use it to link the two tables.

5

Hi, this Works well. But, i run into another problem. In the RevenueBudget data there is 53 weeks, not 52.

Week 53 is the last days in december, when the January 1st is in the middle of the week. Any experience how to handle week 53 in a scenario like this ?

6

Please view this video from Sam on how to properly setup a Date Table. This should address all of your concerns. I have also attached another version of the Date Table code that @luisa1989 posted earlier in this thread. I got this from @Melissa in a previous post here on the forum.

Power BI Date Table Code with Offsets.txt (4.7 KB)

Thanks
Jarrett

7

Thank you, my New DateTable also include week 53 :smile:, but im still missing a Complete solution on the scenario. Since week 53 is an issue, i cant use the first solution to filter the date table on sundays (last day in week).

I think, if i create a New Field in the Revenue-Bugdet Field that calculate the last date in week and set relationship between this LastWeekDate Field and the date Field in the DateTable - it should work?

Any idea how to calculate last date in a week, when we use the Year and Week number Field ?

Revenue-budget.pbix (130.2 KB)

8

You have a column in the Date Table called" Week Ending". This will give you a date field that is every Sunday. Let me know if this solves your issue. You will also need to un filter your Date Table. You have it only showing Sunday right now. The go in and mark your Date Table as "Date Table. I have attached a post that explains this:

image
image177×691 5.58 KB

** You will also need to adjust your RevenueBudget Table. It really needs a Date Field so that you can create a proper relationship between it and the Date Table.**

** I have also attached your file with the Date Table marked as Date Table**
Revenue-budget (3).pbix (151.9 KB)

Thanks
Jarrett

1 Like
9

Hi @TrulsB, we’ve noticed that no response has been received from you since the 3rd of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!