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.
Hello All,
I was wondering if anyone could help me with this problem i am having. I have tried different ways to do it but came up short.
My problem is :
REFERENCE | LAST ENTRY DATE | STOCK ENTRY QUANTITY | COST |
A | 12/01/2017 | 150 | $ 1.00 |
B | 20/01/2017 | 100 | $ 5.00 |
B | 12/03/2017 | 150 | $ 4.50 |
A | 12/04/2017 | 200 | $ 1.10 |
A | 02/07/2017 | 50 | $ 0.98 |
B | 12/07/2017 | 120 | $ 4.75 |
S | 25/07/2017 | 150 | $ 2.00 |
D | 23/08/2017 | 200 | $ 4.40 |
F | 27/08/2017 | 150 | $ 145.00 |
F | 12/01/2018 | 140 | $ 130.00 |
S | 20/01/2018 | 80 | $ 1.80 |
D | 12/02/2018 | 200 | $ 4.15 |
And i want to get this :
REFERENCE | LAST ENTRY DATE | STOCK ENTRY QUANTITY | COST | CALCULATED COLUM |
A | 12/01/2017 | 150 | $ 1.00 | |
B | 20/01/2017 | 100 | $ 5.00 | |
B | 12/03/2017 | 150 | $ 4.50 | |
A | 12/04/2017 | 200 | $ 1.10 | |
A | 02/07/2017 | 50 | $ 0.98 | $ 0.98 |
B | 12/07/2017 | 120 | $ 4.75 | $ 4.75 |
S | 25/07/2017 | 150 | $ 2.00 | |
D | 23/08/2017 | 200 | $ 4.40 | |
F | 27/08/2017 | 150 | $ 145.00 | |
F | 12/01/2018 | 140 | $ 130.00 | $ 130.00 |
S | 20/01/2018 | 80 | $ 1.80 | $ 1.80 |
D | 12/02/2018 | 200 | $ 4.15 | $ 4.15 |
So i can later make a table (visualizations) to show me this :
REFERENCE | CALCULATED COLUM |
A | $ 0.98 |
B | $ 4.75 |
F | $ 130.00 |
S | $ 1.80 |
D | $ 4.15 |
Thanks in advance.
Solved! Go to Solution.
Hi,
This measure works
=if(HASONEVALUE(Table1[REFERENCE]),LOOKUPVALUE(Table1[COST],Table1[LAST ENTRY DATE],LASTNONBLANK(Table1[LAST ENTRY DATE],SUM(Table1[COST])),Table1[REFERENCE],VALUES(Table1[REFERENCE])),BLANK())
Hope this helps.
Hello @NFI27,
Try this calculed colum;
MaxValue = VAR MAXVALUE = CALCULATE( MAX(Table1[LAST ENTRY DATE]); FILTER( Table1; Table1[REFERENCE]=EARLIER(Table1[REFERENCE]))) RETURN IF(Table1[LAST ENTRY DATE]=MAXVALUE; [ COST ])
Greets,
Ronald
Hello @NFI27,
Try this calculed colum;
MaxValue = VAR MAXVALUE = CALCULATE( MAX(Table1[LAST ENTRY DATE]); FILTER( Table1; Table1[REFERENCE]=EARLIER(Table1[REFERENCE]))) RETURN IF(Table1[LAST ENTRY DATE]=MAXVALUE; [ COST ])
Greets,
Ronald
Hi,
This measure works
=if(HASONEVALUE(Table1[REFERENCE]),LOOKUPVALUE(Table1[COST],Table1[LAST ENTRY DATE],LASTNONBLANK(Table1[LAST ENTRY DATE],SUM(Table1[COST])),Table1[REFERENCE],VALUES(Table1[REFERENCE])),BLANK())
Hope this helps.
Worked perfectly. Thanks
You are welcome.
I have a similar problem https://community.powerbi.com/t5/Desktop/DAX-first-value-of-group/td-p/609670/jump-to/first-unread-m... but I think I can help with yours
Add the calculated column :
LATEST ENTRY DATE = CALCULATE( MAX('Table'[LAST ENTRY DATE]); ALLEXCEPT('Table';'Table'[REFERENCE]) )
Then add the measure :
CALCULATED COLUMN = CALCULATE( SUM('Table'[ COST ]); FILTER( 'Table'; 'Table'[LAST ENTRY DATE]='Table'[LATEST ENTRY DATE] ) )
It should do the trick
Hello Raimana,
I tried it but i have a problem. It shows me the value of all the enties together:
REFERENCE | LAST ENTRY DATE | STOCK ENTRY QUANTITY | COST | CALCULATED COLUM |
A | 12/01/2017 | 150 | $ 1.00 | |
B | 20/01/2017 | 100 | $ 5.00 | |
B | 12/03/2017 | 150 | $ 4.50 | |
A | 12/04/2017 | 200 | $ 1.10 | |
A | 02/07/2017 | 50 | $ 0.98 | $ 0.98 |
B | 12/07/2017 | 120 | $ 4.75 | $ 4.75 |
S | 25/07/2017 | 150 | $ 2.00 | |
D | 23/08/2017 | 200 | $ 4.40 | |
F | 27/08/2017 | 150 | $ 145.00 | |
F | 12/01/2018 | 140 | $ 130.00 | $ 130.00 |
S | 20/01/2018 | 80 | $ 1.80 | $ 1.80 |
D | 12/02/2018 | 200 | $ 4.15 | $ 4.15 |
instead of showing me this:
REFERENCE | CALCULATED COLUM |
A | $ 0.98 |
B | $ 4.75 |
F | $ 130.00 |
S | $ 1.80 |
D | $ 4.15 |
It shows me this:
REFERENCE | LAST ENTRY DATE | STOCK ENTRY QUANTITY | COST |
A | 02/07/2017 | 50 | $3.08 |
B | 12/07/2017 | 120 | $14.25 |
F | 12/01/2018 | 140 | $275.00 |
S | 20/01/2018 | 80 | $3.80 |
D | 12/02/2018 | 200 | $8.55 |
and i dont know how to fix this problem
You shouldn't add COST in the table but CALCULATED MEASURE.
Covering 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 |
---|---|
114 | |
100 | |
74 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |