Forum Discussion

Upali63's avatar
Upali63
Helper II
3 years ago
Solved

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    ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

     

v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

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.