Forum Discussion
Blank All But Earliest Date
I am trying to create a new table with only the earliest entered date for each incident number. I am reasonably new to PowerBI.
I have multiple entry dates for an incident as an entry date is created each time an incident is reviewed. I only want a table of the incident number and the single earliest date of entry (entered_date)
So it would be like;
22486 and 30/1/2017
22507 and 31/1/2017
You could do this without another table. In your existing table you could create a new column and use this:
Earliest Date = VAR IncidentVal = [Incident Number] RETURN CALCULATE( FIRSTDATE([Entered_Date]), ALL('Your Table'), 'Your Table'[Incident Number] = IncidentVal )If you then wished to exclude any line item that is not the earliest date, you could simply create another Flag column like this:
FirstRecord = [Entered_Date] = [EarliestDate]
Now you can set your reports to exclude any records where "First Record" is false. The only logic assumption here is that its not possible to have two records entered on the same date. If that was true, you would could get 2 records both created on the same date and that could potentially be your first date.
EDIT: Made a typo
2 Replies
You could do this without another table. In your existing table you could create a new column and use this:
Earliest Date = VAR IncidentVal = [Incident Number] RETURN CALCULATE( FIRSTDATE([Entered_Date]), ALL('Your Table'), 'Your Table'[Incident Number] = IncidentVal )If you then wished to exclude any line item that is not the earliest date, you could simply create another Flag column like this:
FirstRecord = [Entered_Date] = [EarliestDate]
Now you can set your reports to exclude any records where "First Record" is false. The only logic assumption here is that its not possible to have two records entered on the same date. If that was true, you would could get 2 records both created on the same date and that could potentially be your first date.
EDIT: Made a typoThanks Ross I changed it a bit to use Min not FirstDate as I had duplicate date values but works all the same
