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

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.