Forum Discussion
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 Reply
Anonymous add a measure
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!
