Weekly Sum from daily Transaction table

[Upali63]

Hi Friends

I have following weeky data in my table

Week             Code, Price, Earning  

05-01-23          RCL     20      2000

13-01-23          RCL     22      2000

05-01-23          LWL     30      1500

20-01-23          LWL     32      3000

Then I have undermetioned daily trasaction in another table

Date            code      qty    price

01-01-23     RCL        100    19

03-01-23     RCL        200    20

13-01-23     RCL        150    19

04-01-23     LWL        200    30

19-01-23     LWL        100    31

20-01-23     LWL        150    29

Then I want following Table report in my dashboard

Week             Code, Price, Earning  Volume

05-01-23          RCL     20      2000   5900  (qty * Price summaried on weekly basis)

13-01-23          RCL     22      2000   2850

05-01-23          LWL     30      1500  6000

20-01-23          LWL     32      3000  7450

I am new to power Bi kindly help me achieve above result

[v-jingzhang · Accepted solution]

Hi @Upali63 

Not sure why the first week date is "05-01-23" in your sample table. From the calendar, it seems it should be "06-01-23" so I corrected it into "06-01-23" in Table 1. 

Then you can create a new column with below DAX in Table 1. 

Volume = SUMX(FILTER('Table 2','Table 2'[Code]='Table 1'[Code] && 'Table 2'[Date]<='Table 1'[Week] && 'Table 2'[Date]>'Table 1'[Week]-7), 'Table 2'[Price]*'Table 2'[Qty])

Add columns from Table 1 into a table visual in the report. The sample pbix has been attached at bottom. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.