cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Return value with earliest date

Hi everyone, i need some help with measure/table please.... I am pretty new to Power BI

The dataset that i have contains a number of deals in different locations and each location has different dates and the number of people.

 Client Deal Location Date Number of people A A1 London 01/02/2020 5 B B1 Tokyo 01/05/2020 10 B B2 Sydney 01/06/2020 25 C C1 London 15/07/2020 20 C C1 London 20/07/2020 10 C C1 London 30/07/2020 5 C C2 Singapore 01/08/2020 20

And what I would like to create is a table which shows the number of people for the earliest date in each location like below. Is there a way to do this using a measure? I tried with the measure below but did not work... I suspect it's because of SUM?

Measure  = CALCULATE(SUM(Number of people), FILTER(Table, MIN(Table[Date].[Date]))

 Client Deal Location Date Number of people A A1 London 01/02/2020 5 B B1 Tokyo 01/05/2020 10 C C1 London 15/07/2020 20

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VII

## Re: Return value with earliest date

``````Measure = VAR __earlierDate = CALCULATE ( MIN ( Data[Date] ), ALLEXCEPT ( Data, Data[Client] ) )
RETURN CALCULATE ( SUM ( Data[Number of people] ), ALLEXCEPT ( Data, Data[Client] ), Data[Date] = __earlierDate )``````

here is the output

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Super User VII

## Re: Return value with earliest date

``````Measure = VAR __earlierDate = CALCULATE ( MIN ( Data[Date] ), ALLEXCEPT ( Data, Data[Client] ) )
RETURN CALCULATE ( SUM ( Data[Number of people] ), ALLEXCEPT ( Data, Data[Client] ), Data[Date] = __earlierDate )``````

here is the output

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors