Forum Discussion

SamTaylor's avatar
SamTaylor
Helper I
6 years ago
Solved

Power Pivot Filters

 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...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    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. ğŸ™‚

     

SamTaylor's avatar
SamTaylor
Helper I
6 years ago

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 

 

ProductLocationGRNQtySupplierReceipt Date
LK-049UKGR 22320Howhot10/07/2020
LK-049USGR 2245Howhot11/07/2020
LK-047UKGR 22320Howhot10/07/2020
LK-047USGR 2245Howhot11/07/2020
LK-048UKGR 22320Howhot10/07/2020
LK-049UKGR 22520Howhot14/07/2020
LK-049UKGR 12320Howhot10/06/2020
LK-049USGR 1245Howhot11/06/2020
LK-047UKGR 12320Howhot10/06/2020
LK-047USGR 1245Howhot11/06/2020
LK-048UKGR 12320Howhot10/06/2020
LK-049UKGR 12520Howhot14/06/2020
SamTaylor's avatar
SamTaylor
Helper I
6 years ago

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

 

 

 

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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's avatar
      SamTaylor
      Helper I
      6 years ago

      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's avatar
        edhans
        Community Champion
        6 years ago

        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. ğŸ™‚