Forum Discussion
Weekly Sum from daily Transaction table
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
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.
1 Reply
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.