cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leharkapil Member
Member

Take max of date by column

Hi,

 

I have a dataset attached below : 

 

DealerDateMaturityOutput DateOutput Maturity
H10002018-01-01Low2018-01-04High
H10002018-01-02Medium2018-01-04High
H10002018-01-03High2018-01-04High
H10002018-01-04High2018-01-04High
N05002018-03-06Low2018-03-08Medium
N05002018-03-07Low2018-03-08Medium
N05002018-03-08Medium2018-03-08Medium

 

I want to calculate max date for each dealer name and

the output should have a single maturity and single date for each dealer.

Output column is attached.

 

Please help

1 ACCEPTED SOLUTION

Accepted Solutions
ryan_mayu Established Member
Established Member

Re: Take max of date by column

@leharkapil

 

Please try the below coding to see if this can meet your requirements.Thanks.

 

Column = MAXX(FILTER(Sheet33,Sheet33[dealer]=EARLIER(Sheet33[dealer])),'Sheet33'[DATE])
Column 2 = 
VAR MAXDATE = MAXX(FILTER(Sheet33,Sheet33[dealer]=EARLIER(Sheet33[dealer])),'Sheet33'[DATE])
RETURN MAXX(FILTER('Sheet33',Sheet33[dealer]=EARLIER(Sheet33[dealer])&&'Sheet33'[DATE]=MAXDATE),Sheet33[Maturity])

c1.JPG

 

3 REPLIES 3
ryan_mayu Established Member
Established Member

Re: Take max of date by column

@leharkapil

 

Please try the below coding to see if this can meet your requirements.Thanks.

 

Column = MAXX(FILTER(Sheet33,Sheet33[dealer]=EARLIER(Sheet33[dealer])),'Sheet33'[DATE])
Column 2 = 
VAR MAXDATE = MAXX(FILTER(Sheet33,Sheet33[dealer]=EARLIER(Sheet33[dealer])),'Sheet33'[DATE])
RETURN MAXX(FILTER('Sheet33',Sheet33[dealer]=EARLIER(Sheet33[dealer])&&'Sheet33'[DATE]=MAXDATE),Sheet33[Maturity])

c1.JPG

 

leharkapil Member
Member

Re: Take max of date by column

Hi,

 

Im new to power bi, could you please explain how does earlier operate. The documentation is confusing

Community Support Team
Community Support Team

Re: Take max of date by column

@leharkapil,

 

You may check post below.

https://community.powerbi.com/t5/Desktop/This-Function/m-p/326489#M145679

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.