filter table by another table based on Max week

[mb0307]

Hi 

Max Week Table below shows Month-Year and Max week of the month:

Month Year	Max/Last Week for the Month
Jan-20	5
Feb-20	9
Mar-20	14
Apr-20	18

SALES table below:

|  Product ID  |	|  Month Year  |	|  Sales  |	|  Data Snapshot week  |
P01	Jan-20	500	5
P01	Jan-20	1500	5
P02	Feb-20	1000	9
P02	Feb-20	2000	11
P01	Jan-20	1500	13
P03	Apr-20	4000	18
P03	Jan-20	100	5
P03	Jan-20	200	5

I want output to show a summarized table but Month-Year, Products and Sales SUM from the SALES table, but to be filtered by:  IF Data Snapshot Week = Max/Last Week for the Month.  Rows highlighted in RED should be removed from result table because they do not match Max week table.

Result:

|  Product ID  |	|  Month Year  |	|  Sales SUM  |	|  Max/Last  Week for the Month  |
P01	Jan-20	2000	5
P02	Feb-20	1000	9
P03	Jan-20	300	5
P03	Apr-20	4000	18

Thank you in advance.

[parry2k]

@mb0307 set the relationship between these two tables on Max/Last  week for the month

in the visual, use column from Max Week table and product, sales from 2nd table and it should work.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mb0307]

@parry2k  Thanks but I would like to create a new table based on my query.  I need to use the table to run some complex calculation.

[v-yiruan-msft · Accepted solution]

Hi @mb0307 ,

First, create a relationship between Max Week and Sales table base on the field [Month Year], then create a calculated table as below:

Result =
SUMMARIZECOLUMNS (
    'Sales'[Product ID],
    'Sales'[Month Year],
    'Max Week'[Max/Last Week for the Month],
    'Sales',
    "Sales SUM", CALCULATE (
        SUM ( 'Sales'[Sales] ),
        FILTER (
            'Sales',
            'Sales'[Data Snapshot week] = MAX ( 'Max Week'[Max/Last Week for the Month] )
        )
    )
)

Best Regards

Rena

[mb0307]

@v-yiruan-msft  exactly what i need.  Thanks for your help, much appreciated.