Forum Discussion

mb0307's avatar
mb0307
Responsive Resident
6 years ago
Solved

filter table by another table based on Max week

Hi 

 

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

Month YearMax/Last Week for the Month
Jan-205
Feb-209
Mar-2014
Apr-2018

 

 

SALES table below:

|  Product ID  ||  Month Year  ||  Sales  ||  Data Snapshot week  |
P01Jan-205005
P01Jan-2015005
P02Feb-2010009
P02Feb-20200011
P01Jan-20150013
P03Apr-20400018
P03Jan-201005
P03Jan-202005

 

 

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  |
P01Jan-2020005
P02Feb-2010009
P03Jan-203005
P03Apr-20400018

 

Thank you in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      mb0307
      Responsive Resident
      6 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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