Calculate weekly sum from different tables

[Anonymous]

Hi,

I'm a newbe for Power BI and can't see how to solve this calculation.

I have different tables:

Table 1:   has arrival_date and amount fields

Table 2:  Calendar table, includes weeknumber of year

Table 3:  I need to calculate the weekly sum for the amount in table 1 (at the end of the week) and show, and also compare each weekly sum with last week's sum.

Example:  

TABLE 1

Date	Reservation	Amount
1/5/2021	123	100
2/5/2021	234	200
5/5/2021	1234	150
8/5/2021	1236	300

TABLE 2

Date	WeekNum
1/5/2021	17
2/5/2021	17
3/5/2021	18
4/5/2021	18
6/5/2021	18
7/5/2021	18
8/5/2021	18

TABLE 3

DATE	WEEKNR	WEEKLY_SUM	DIFF_LAST_WEEK
1/5/2021	17	100	-
2/5/2021	17	300	-
3/5/2021	18	0	-300
4/5/2021	18	0	-300
5/5/2021	18	150	-150
6/5/2021	18	150	-150
7/5/2021	18	150	-150
8/5/2021	18	450	150

How can I achieve this? 

Do I have to do that in Transform Data > Add columns (Power Query, right?)

Or can I do it with DAX from the main Power Desktop Menu adding a new table and columns? 

Thanks a lot in advance for all your help! 🙂

Regards,

Victor. 

[Anonymous]

Wow..this was fast. And efficient! 

Thanks a lot!  No wonder why you're a Community Champion! 🙂

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I suggest doing by DAX measure.

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

https://www.dropbox.com/s/tw6eiw0quw5ba7f/vicgonsam.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/