Power Pivot Filters

[SamTaylor]

 I have a table with Goods Receipt data for my products, goinbg back 2-3 years.

Product, Supplier, GRN Number, PO Number, Reciept Date, Quantity Received

I need to show in a report, by product, the last receipt data and quantity received on that date

I am using Power Query and Power Pivot.  

Thanks

Sam

[v-eachen-msft]

Hi @SamTaylor ,

I am not clear about your requirements. Do you mean to get an accumulated value or a maximum?

[SamTaylor]

I have a table with Goods Receipt data for my products, goinbg back 2-3 years.

Product, Supplier, GRN Number, PO Number, Reciept Date, Quantity Received

I need to show in a report, by product, the last receipt data and quantity received on that date

I am using Power Query and Power Pivot.  

For example, I have a product received on 3 GRNS, one Jan, one Feb and one Mar, each with a qty of 5.

The report needs to show me the last Grn = Mar, the qty received in Mar and if possible the supplier name.

Thanks

Sam

[SamTaylor]

Sorry, to be clear.

I need the data to be available in Power Pivot, so I can create reports from the data.

Cheers

Sam

[edhans]

Again, if you could post some sample data that is representative or your actual data (same columns, enough rows to work with, etc) it would help us. Just writing a paragraph about your data layout isn't helping me see your model very well. Tables are worth a thousand words. 😁

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[SamTaylor]

Please see below my test data for the GRN table.

I need the ability to include in my pivot table the last receipt date for a Product and the Qty received, in the location UK or US.

I can work it out for Total Qty received by product by location, just escapes me how to get the qty for the last receipt date.

@edhans 

Thanks

Sam 

Product	Location	GRN	Qty	Supplier	Receipt Date
LK-049	UK	GR 223	20	Howhot	10/07/2020
LK-049	US	GR 224	5	Howhot	11/07/2020
LK-047	UK	GR 223	20	Howhot	10/07/2020
LK-047	US	GR 224	5	Howhot	11/07/2020
LK-048	UK	GR 223	20	Howhot	10/07/2020
LK-049	UK	GR 225	20	Howhot	14/07/2020
LK-049	UK	GR 123	20	Howhot	10/06/2020
LK-049	US	GR 124	5	Howhot	11/06/2020
LK-047	UK	GR 123	20	Howhot	10/06/2020
LK-047	US	GR 124	5	Howhot	11/06/2020
LK-048	UK	GR 123	20	Howhot	10/06/2020
LK-049	UK	GR 125	20	Howhot	14/06/2020

[SamTaylor]

This is an image of the table, seemed to get mixed up when i posted the original

[edhans]

Hi @SamTaylor , thank you for the sample data. I wrote a measure that returns this, which is the quantity for the last receipt date of a product by location. Given that your quantites for UK and US are always the same, it is hard to see that my measure works though i this screen shot.

Last Qty and Date by Location = 
VAR varCurrentLocation =
    MAX( 'Table'[Location] )
VAR varCurrentProduct =
    MAX( 'Table'[Product] )
VAR varLastDateByLocation =
    MAXX(
        FILTER(
            ALL( 'Table' ),
            'Table'[Location] = varCurrentLocation
                && 'Table'[Product] = varCurrentProduct
        ),
        'Table'[Receipt Date]
    )
VAR Result =
    CALCULATE(
        SUM( 'Table'[Qty] ),
        FILTER(
            ALL( 'Table' ),
            'Table'[Location] = VarCurrentLocation
                && 'Table'[Product] = varCurrentProduct
                && 'Table'[Receipt Date] = varLastDateByLocation
        )
    )
RETURN
    Result

[SamTaylor]

@edhans 

OMG, it's excellent. I thought I might be able to pick up some Dax learning on the side but it's way above me.

I have applied it to the test table below and the result is in the pivot below that. (see image)

But now I am fealing guilty as I need a minor amend, due to my lack of clarity at the start.

I need the UK and US results to be in separate measures, as in the green table.  Is this a quick change?

Also, my wife makes candles, can I send you one as a gift?

see www.wrightandgreen.co.uk for a selection of scents.

Thanks

Sam

[edhans · Accepted solution]

Yes, small tweaks, but instead of one measure it is now 4, so rather than post them all here, just grab the PBIX file. It returns a table like this:

I appreciate the candle offer, but I'm just here to help. 🙂

[edhans]

You are going to have to share data, otherwise you'll just get generic answers about using MAX() in a variable for the date, the ALL() function, etc. 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum