cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Vauban
Frequent Visitor

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

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.

Original data and with custom column.pngSample data on left and on right with desired custom column.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

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

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])




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


View solution in original post

6 REPLIES 6
JoseCruzCat Helper I
Helper I

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

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])
)
) 
Super User IV
Super User IV

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

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])




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Vauban
Frequent Visitor

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

Hi and thank you for replying @JoseCruzCat 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

Screen shot of error in formula.png

JoseCruzCat Helper I
Helper I

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

Change "," by &&
Super User IV
Super User IV

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

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])




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


View solution in original post

Vauban
Frequent Visitor

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

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

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

All the best,

David

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors