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.
Hi, I have two tables, one with our current products (with product code) and another with the historic price of those codes.
For example:
Table A
P.Code Actual Price
334353 34.3
Table B
Date P.Code Price
2/01/2019 334353 32.1
5/01/2020 334353 32.3
7/01/2020 334353 35.2
How do I obtain the last know price for the product?
Table A (new)
P.Code Actual Price Last Price
334353 34.3 35.2
I thought a possibility is making a summarized new table of Table B and relate this with Table A by P. Code but I didn't figure out how to do it.
Thank you very much in advance.
Solved! Go to Solution.
Hi @marcos_osorio ,
Try measure like these:
Measure 3 = var p = MAX('Table A'[P.Code])
return CALCULATE(LASTNONBLANKVALUE('Table C'[Date],MAX('Table C'[Price])),FILTER('Table C','Table C'[P.Code]=p))
OR
Measure = var p_code = MAX('Table A'[P.Code])
var lastest_date = CALCULATE(MAX('Table B'[Date]),ALLEXCEPT('Table B','Table B'[P.Code]))
return CALCULATE(MAX('Table B'[Price]),FILTER('Table B','Table B'[Date]=lastest_date&&'Table B'[P.Code]=p_code))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @marcos_osorio ,
Try measure like these:
Measure 3 = var p = MAX('Table A'[P.Code])
return CALCULATE(LASTNONBLANKVALUE('Table C'[Date],MAX('Table C'[Price])),FILTER('Table C','Table C'[P.Code]=p))
OR
Measure = var p_code = MAX('Table A'[P.Code])
var lastest_date = CALCULATE(MAX('Table B'[Date]),ALLEXCEPT('Table B','Table B'[P.Code]))
return CALCULATE(MAX('Table B'[Price]),FILTER('Table B','Table B'[Date]=lastest_date&&'Table B'[P.Code]=p_code))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@marcos_osorio , A new column in Table 1
calculate(lastnonblankvalue(Table2[Date],Max(Table2[price])), filter(Table2, Table2[P.Code] = Table1[P.Code]))
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |