Forum Discussion
Create a column with rows based on earliest value in another column
I have the below table and want to create a column that has the first value of weight (152.6) for every row. I plan on using this to create another measure that shows % increase decrease over time
What is the easiest way to do this?
Thanks for your help
| Date | Time | Weight |
| 1/29/2023 | 11:59:00 AM | 152.6 |
| 1/29/2023 | 6:44:00 PM | 153 |
| 1/30/2023 | 7:48:00 AM | 152.8 |
| 1/30/2023 | 8:45:00 PM | 151.4 |
| 1/31/2023 | 6:34:00 AM | 150.8 |
Hi,
Write these calculated column formulas
Date time = Data[Date]+Data[Time]First weight = LOOKUPVALUE(Data[Weight],Data[Date time],CALCULATE(MIN(Data[Date time]),ALL(Data)))Hope this helps.
10 Replies
hummingbirdHelper II
Hi,
You could try something like this. Although I would suggest linking the "earliestWeight" to "Time" column, so you get the true first value, rather than just the minimum results from the "Weight" column.
Average Weight = VAR earliestWeight = CALCULATE ( MIN ( 'Table'[Weight] ), FILTER ( 'Table', 'Table'[Date] = MIN ( 'Table'[Date] ) ) ) RETURN CALCULATE(AVERAGE( 'Table'[Weight]), ALLEXCEPT('Table','Table'[Date])) / earliestWeight- hummingbird
rbpercussion Does that answer your question? Mark it as a solution!
- Ashish_Mathur
Super User
Hi,
Write these calculated column formulas
Date time = Data[Date]+Data[Time]First weight = LOOKUPVALUE(Data[Weight],Data[Date time],CALCULATE(MIN(Data[Date time]),ALL(Data)))Hope this helps.
- Greg_Deckler
Community Champion
rbpercussion Try:
Column = MINX(ALL('Table'),[Weight])Hi Greg_Deckler ,
That returns the minimum weight for the table. What I am trying to return is the weight that corresponds to the earliest date
- serpiva64
Solution Sage
Hi,
you can try
Measure = CALCULATE(sum('Table'[Weight]), INDEX(1,ORDERBY('Table'[Merged])))Column D is my desired output and how I would write it in excel:
This is my desired output in column D and how I would write it in excel:
Ashish_Mathur this worked! thank you!
- Ashish_Mathur
You are welcome.
