Forum Discussion
Search same value in same table between two dates
I have one table which has my products.
For each product finds if it was listed in a specific month in the past (the minimun of the date range, here lets say [oct, nov,dec,jan] with min = oct and current=jan) and score each product as following:
1- Product present in both months 100
2- If Newly listed then score is 50
3- If was present in the reference period and not listed now then -50
I have only table as follows
| Date | Productid | price |
| 1-Jan | 1 | 10 |
| 1-Dec | 1 | 20 |
| 1-Nov | 1 | 30 |
| 1-Oct | 1 | 40 |
| 1-Jan | 2 | 10 |
| 1-Dec | 2 | 12 |
| 1-Nov | 2 | 13 |
| 1-Jan | 3 | 12 |
| 1-Dec | 3 | 14 |
| 1-Nov | 3 | 15 |
| 1-Oct | 3 | 29 |
| 1-Dec | 4 | 14 |
| 1-Nov | 4 | 15 |
| 1-Oct | 4 | 29 |
Desired result shoul be
| Product | score |
| 1 | 100 |
| 2 | 50 |
| 3 | 100 |
| 4 | -50 |
I will appreciate your help
amitchandak Thank you very much.
It works as expected. Really thank you! this saves long hours of work
2 Replies
I converted month into date to work with
Measure = var _min = minx(all(Sheet1),Sheet1[Date]) var _max = maxx(all(Sheet1),Sheet1[Date]) var _before = CALCULATE(max(Sheet1[price]),filter((Sheet1),Sheet1[Date]=_min)) var _after = CALCULATE(max(Sheet1[price]),filter((Sheet1),Sheet1[Date]=_max)) Return //maxx(VALUES(Sheet1[Productid]),_before & " "& _after) if(ISBLANK(_after),-50,if(not(ISBLANK(_before)),100,50))You can use first or last date of month
link: https://www.dropbox.com/s/kvazja8tnzqzq6a/samevalue.pbix?dl=0
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedinamitchandak Thank you very much.
It works as expected. Really thank you! this saves long hours of work
