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 Year Max/Last Week for the Month Jan-20 5 Feb-20 9 Mar-20 14 Apr-20 18     SALES table below:...
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

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

    • mb0307's avatar
      mb0307
      Responsive Resident
      6 years ago

      Anonymous  exactly what i need.  Thanks for your help, much appreciated.