Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I've spent the last few hours on this and decided I need help. I'm quite new to Power BI so still feeling my way around, although an experienced in Excel.
I have multiple tables based in a SQL database/warehouse (using Direct Query).
One of which contains the standard price for a product (MBEWH[STPRS]), another table contains the transactions with the sold volume (VBRP[FKING]. The material number is [MATNR] in both tables and I want use the most recent Std Cost based on two columns - Year (MBEWH[LFGJA]) and Month (MBEWH[LFMON]).
I would like to calculate the cost of the total product sold but can't get the lookup correct. I've tried LOOKUPVALUE but can't get it to work. I also tried a FIRSTNONBLANK suggestion from another post but couldn't get it to work either.
I've hit a brick wall and not sure where to go next? Any assistance on a way to handle the lookup would be greatly appreciated.
Something I forgot to add in the original post was the need to filter based on site - MBEWH[BWKEY] & VBRP[VSTEL]
I hope this link provides enough detail
https://www.dropbox.com/s/9v0ux5e7bq7pu40/Delivered%20Margin_DirectQ%20Cost%20Help.pbix?dl=0
Unfortunately I had to Import the data to truncate the info due to confidentiality, however the original file is DirectQuery tables.
Many thanks for your guidence.
Regards
Stuart
HI @yacatac,
After play with your sample, I can't found some of fields which you mentioned.
For your sample data, I can only do summarize on tables and get unique records based on specific summarize mode.
Regards,
Xiaoxin Sheng
Bump
Hi,
Share the link from where i can download your PBI file. Also, do you want a measure or a caluclated column solution?
Hi @yacatac,
You can try to use below formula to find out most recent product price, then you can use it calculate out most recent cost of total sold:
MR Std Cost = VAR _currNumber = SELECTEDVALUE ( MBEWH[MATNR] ) VAR _lastDate = MAXX ( ADDCOLUMNS ( ALLSELECTED ( MBEWH ), "Merge", [LFGJA] * 100 + [LFMON] ), [Merge] ) RETURN CALCULATE ( LASTNONBLANK ( MBEWH[STPRS], [STPRS] ), FILTER ( ALLSELECTED ( MBEWH ), MBEWH[MATNR] = _currNumber && [LFGJA] = LEFT ( _lastDate, 4 ) && [LFMON] = RIGHT ( _lastDate, 2 ) ) ) Cost of the total product sold = SELECTEDVALUE ( VBRP[FKING] ) * [MR Std Cost]
If above not help, please share a pbix file with some of sample data and expected result, it will be help for test and coding formula.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |