Calculate 5th date from max date

[Anonymous]

Hello All,

I am trying to get the last fifth date from the max date in my table.

EmpIDDateDaytype

A	21/10/2018	WO
A	22/10/2018	AB
A	23/10/2018	AB
B	24/10/2018	AB
A	25/10/2018	AB
A	26/10/2018	AB
A	27/10/2018	WO
A	28/10/2018	WO
A	29/10/2018	AB
A	30/10/2018	AB
B	21/10/2018	WO
B	22/10/2018	AB
B	23/10/2018	AB
B	24/10/2018	AB
B	25/10/2018	AB
B	26/10/2018	AB
B	27/10/2018	WO
B	28/10/2018	WO
B	29/10/2018	AB
B	31/10/2018	AB

This is the data that iam working on.

And my query looks like this.

Last5thDate = CALCULATE(MAX(Table[Date]),
    FILTER(ALLEXCEPT(Table,Table[EmpID]),
        NOT(Table[Daytype]="WO" )))-4

But it gives me wrong output as 26/10/2018 for A and 27 for B

Can any one please help me out.

MohanV

[petrovnikitamai · Accepted solution]

new column = 
var rankk = RANKX(FILTER('Table';EARLIER('Table'[EmpID])='Table'[EmpID]&&EARLIER('Table'[Daytype])='Table'[Daytype]);'Table'[Date];;DESC)
var last5thDate = IF(rankk=5&&[Daytype]<>"WO";[Date];BLANK())
return last5thDate

try this

[Greg_Deckler]

If I am reading your formula correctly, it looks like you want to find the 5th date from the MAX date that doesn't involve WO's, only AB's, is that correct?

[Anonymous]

@Greg_Deckler Yes..you got it right...

Any help

[Greg_Deckler]

@Anonymous - See if this works for you. Page 3 of the attached file (Table6). 

Measure 3 = 
VAR __emplID = MAX('Table6'[EmplID])
VAR __table = FILTER('Table6',[EmplID]=__emplID && [Daytype]="AB")
VAR __table1 = ADDCOLUMNS(__table,"__rank",RANKX(__table,[Date]))
RETURN MAXX(FILTER(__table1,[__rank]=5),[Date])

[Anonymous]

@Greg_Deckler @petrovnikitamai thanks for your help.

Both the solutions works perfectly..

But I am looking for the calculated column as i have some calculations needs to be done based on that, afterthat.

thanks.

[Anonymous]

@Greg_Deckler @petrovnikitamai

What if my data model is looks like this and whenever i get the "NT" value then my rank count should get start from 1 again.

EmpIDDateDaytype

A	21-10-2018	WO
A	22-10-2018	AB
A	23-10-2018	AB
A	24-10-2018	AB
A	25-10-2018	NT
A	26-10-2018	AB
A	27-10-2018	WO
A	28-10-2018	WO
A	29-10-2018	AB
A	30-10-2018	AB
B	21-10-2018	WO
B	22-10-2018	AB
B	23-10-2018	NT
B	24-10-2018	AB
B	25-10-2018	AB
B	26-10-2018	NT
B	27-10-2018	WO
B	28-10-2018	WO
B	29-10-2018	AB
B	31-10-2018	AB

Here For B, On 26/10/2018 it is NT, so when ever i get NT in middle of my DayTpe column then it should count from 1 again.

Any Suggestions

[Greg_Deckler]

Hmm, I solved a count issue that needed to restart periodically, see if my Cthulhu measure in the Quick Measure Gallery provides a way to do this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739