Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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. 🙂
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @SamTaylor ,
I am not clear about your requirements. Do you mean to get an accumulated value or a maximum?
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
Sorry, to be clear.
I need the data to be available in Power Pivot, so I can create reports from the data.
Cheers
Sam
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingPlease 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.
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 |
This is an image of the table, seemed to get mixed up when i posted the original
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
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
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. 🙂
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |