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

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

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'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