Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table like below:
Date Model MSRP Range New MSRP Column
1/1/15 Box 195-200 200-205
1/1/15 Glue 225-230 225-230
2/1/15 Box 195-200 200-205
3/1/15 Box 200-205 200-205
4/1/15 Box 200-205 200-205
5/1/15 Glue 225-230 225-230
I need to create a new column that takes the MSRP from the Lastest Date and uses that as the New MSRP.
Solved! Go to Solution.
Sorry about that,
Please try this
New MSRP Column = VAR myModel = 'Table1'[Model] VAR MaxDate = MAXX( FILTER( 'Table1', 'Table1'[Model] = EARLIER('Table1'[Model]) ),'Table1'[Date]) RETURN CALCULATE(MAX('Table1'[Price Band]),ALL('Table1'),'Table1'[Date] = MaxDate ,'Table1'[Model] = myModel)
This calculated column worked for me
New MSRP Column = VAR MaxDate = MAXX( FILTER( 'Table1', 'Table1'[Model] = EARLIER('Table1'[Model]) ),'Table1'[Date]) RETURN CALCULATE(MAX('Table1'[MSRP Range]),ALL('Table1'),'Table1'[Date] = MaxDate)
Hi Phil_Seamark,
Thank you, I did find your solution to work with the sample data I provided, except when I try to use it with my rows of data something goes wrong.
For example, even when I take a small piece of data it isn't working. Not sure what is going wrong, below is what I am getting:
Date Model Price Band New MSRP Column
1/1/16 SIU82 1,500-2,999 1,500-2,999
1/1/16 SIU22 1,000- 1,499 1,500-2,999
1/1/17 25652 300-499 500-799
1/1/17 25652 300-499 500-799
1/1/17 25652 300-499 500-799
2/1/17 25652 300-499 500-799
3/1/17 25652 500-799 500-799
Sorry about that,
Please try this
New MSRP Column = VAR myModel = 'Table1'[Model] VAR MaxDate = MAXX( FILTER( 'Table1', 'Table1'[Model] = EARLIER('Table1'[Model]) ),'Table1'[Date]) RETURN CALCULATE(MAX('Table1'[Price Band]),ALL('Table1'),'Table1'[Date] = MaxDate ,'Table1'[Model] = myModel)
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |