Want to get min date for each state of each subgroup in table and populate in custom column

[Vauban]

Hi everyone. I've looked around forums and tried a few ways but I can't seem to get a custom column for this.

Thank you to all for your time and all help appreciated.

Also, I'm trying to not use a measure so I can see the intermediate values in the calculations.

Hence my ask re a new column.

The table I'm working with has a number of lines per Work item id.

Each row of each Work item id has a unique date and state that I want to evaluate.

I just want to be able to put the earliest/min date for each state of each work item id.

The sample data below on the left is what the pertinent parts of the table are.

The picture on the right is with the additional calculated column.

When I use this formula I get the minimum date across all the rows, rather than by each Work Item Id.

ALL and ALLEXCEPT needed?

MinDatePerStatePerWorkItemId =calculate(min('PPM for BI'[Date]),filter('PPM for BI','PPM for BI'[Work Item Id]),filter('PPM for BI','PPM for BI'[State] = "Active"))

Best wishes to all.

Sample data on left and on right with desired custom column.

[amitchandak]

You have to create it as new column

MinDatePerStatePerWorkItemId = minx(filter('PPM for BI','PPM for BI'[Work Item Id] = earlier('PPM for BI'[Work Item Id])
						&& 'PPM for BI','PPM for BI'[State] = earlier('PPM for BI','PPM for BI'[State])),'PPM for BI'[Date])

[Vauban]

Hi and thank you for replying @Anonymous and @amitchandak.

Amitchandak, I tried using the formula to create the column but encountered an error with the formula.  See screen shot below.  It appears I can't upload my pbix file to this forum which would make it easier for anyone trying to help.  I've tried for about 30 mins and looked up how the earlier function works to no avail and played with the brackets and added another filter function but that didn't work either.  If you would be so kind as to take a last look at what tweak is needed my day would be awesome.

Thank you.

David

[amitchandak · Accepted solution]

Try

 MinDatePerStatePerWorkItemId = minx(filter('PPM for BI','PPM for BI'[Work Item Id] = earlier('PPM for BI'[Work Item Id])
						&& 'PPM for BI'[State] = earlier('PPM for BI'[State])),'PPM for BI'[Date])

[Vauban]

Yaah!  Thank you @amitchandak! That works perfectly.  Thanks also @Anonymous for chiming in too.

I will read up more on this now that I have correct syntax.

All the best,

David

[Anonymous]

Change "," by &&

[Anonymous]

Hello!!

Are you looking for something like this?

MinState = CALCULATE(MIN(Hoja2[date]);
FILTER(ALL(Hoja2);
    Hoja2[state] = EARLIER(Hoja2[state]) &&
    Hoja2[item] = EARLIER(Hoja2[item])
)
)