Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
Thanks Ross I changed it a bit to use Min not FirstDate as I had duplicate date values but works all the same
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |