DAX:- Count Last 5+ AB values from MAX DATE with some condtions

[Anonymous]

Hello All,

I have a data set as below table.

EmpID      Date              Daytype

128256	21/10/2018	WO
128256	22/10/2018	AB
128256	23/10/2018	AB
128256	24/10/2018	AB
128256	25/10/2018	NT
128256	26/10/2018	AB
128256	27/10/2018	WO
128256	28/10/2018	WO
128256	29/10/2018	AB
128256	30/10/2018	AB
128256	1/11/2018	WO
128256	2/11/2018	AB
128256	3/11/2018	NT
128256	4/11/2018	AB
128256	5/11/2018	AB
128257	21/10/2018	WO
128257	22/10/2018	AB
128257	23/10/2018	AB
128257	24/10/2018	AB
128257	25/10/2018	NT
128257	26/10/2018	AB
128257	27/10/2018	WO
128257	28/10/2018	WO
128257	29/10/2018	AB
128257	30/10/2018	AB
128257	1/11/2018	WO
128257	2/11/2018	HL
128257	3/11/2018	AB
128257	4/11/2018	AB
128257	5/11/2018	AB

From above table i am trying to calculate the values of AB from max date.

I.e until and unless NT value does'nt shows up, it should continue to count only AB values, even though if WO or HL values shows up in middle.
* It should print the value at only MAX date.

The expected output looks like this.

EmpID            Date        Daytype  ABCount

128256	21/10/2018	WO
128256	22/10/2018	AB
128256	23/10/2018	AB
128256	24/10/2018	AB
128256	25/10/2018	NT
128256	26/10/2018	AB
128256	27/10/2018	WO
128256	28/10/2018	WO
128256	29/10/2018	AB
128256	30/10/2018	AB
128256	1/11/2018	WO
128256	2/11/2018	AB
128256	3/11/2018	NT
128256	4/11/2018	AB
128256	5/11/2018	AB	2
128257	21/10/2018	WO
128257	22/10/2018	AB
128257	23/10/2018	AB
128257	24/10/2018	AB
128257	25/10/2018	NT
128257	26/10/2018	AB
128257	27/10/2018	WO
128257	28/10/2018	WO
128257	29/10/2018	AB
128257	30/10/2018	AB
128257	1/11/2018	WO
128257	2/11/2018	HL
128257	3/11/2018	AB
128257	4/11/2018	AB
128257	5/11/2018	AB	6

Can anyone please suggest me that how we can do this with DAX.

[PattemManohar · Accepted solution]

@Anonymous Please try this as a "New Column"

Result = 
VAR _MaxDateAB = CALCULATE(MAX(Test67[Date]),FILTER(Test67,Test67[EmpID] = EARLIER(Test67[EmpID]) && Test67[DayType] = "AB" ))
VAR _MaxDateNT = CALCULATE(MAX(Test67[Date]),FILTER(Test67,Test67[EmpID] = EARLIER(Test67[EmpID]) && Test67[DayType] = "NT" ))
VAR _Count = CALCULATE(COUNTROWS(Test67),FILTER(Test67,Test67[DayType] = "AB" && Test67[Date] > _MaxDateNT && Test67[EmpID] = EARLIER(Test67[EmpID])))
VAR _CurrEmpID = Test67[EmpID]
RETURN IF(Test67[EmpID] = _CurrEmpID && Test67[Date] = _MaxDateAB,_Count,BLANK())

[Anonymous]

Wow @PattemManohar... Thats was quick and awsome..

It worked like a charm.

thank you soo much...

Kudos...

[PattemManohar]

@Anonymous Thank You !!