Forum Discussion
Renjith
Helper II
Helper IIDAX Help - To get the first reading based on date field
First Reading
Hi
Please help to acheive this
I need to create a calculated column which takes the first reading (based on date) for each ids
Hi Renjith ,
Two ways to achieve that.
Calculated column:
First reading cal = VAR mi = CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) RETURN CALCULATE ( SUM ( 'Table'[Reading] ), FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) && 'Table'[Date] = mi ) )Measure:
First reading measure = VAR mind = CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) RETURN CALCULATE ( SUM ( 'Table'[Reading] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Date] = mind ) )
4 Replies
GordonliljSolution Sage
Hi,
You could try the solution provided in this post
Finding first value with same ID in multiple rows based on date/time
RenjithThank You
- v-frfei-msft
Community Support
Hi Renjith ,
Two ways to achieve that.
Calculated column:
First reading cal = VAR mi = CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) RETURN CALCULATE ( SUM ( 'Table'[Reading] ), FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) && 'Table'[Date] = mi ) )Measure:
First reading measure = VAR mind = CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) RETURN CALCULATE ( SUM ( 'Table'[Reading] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Date] = mind ) ) - Ashish_Mathur
Super User
Hi,
This calculated column formula works
=LOOKUPVALUE(Data[Reading],Data[Date],CALCULATE(MIN(Data[Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID]))),Data[ID],Data[ID])
Hope this helps.
