Incorrect Earlier calculation

[PBI5851]

Hi,

 I have to capture the count of Account for a specific region whose status is current = "Receive" but the value immediately prior to that should have been 'Call".  My data is as below. 

PrimaryID	AcctiD	Category	Start	KeyVal	Status	Sequence
J1	A1001	East	1/1/2020	Open	Previous	1
J2	A1001	East	1/2/2020	Call	Previous	2
J3	A1001	East	1/3/2020	Receive	Current	3
J4	B1001	West	1/2/2020	Open	Current	1
J5	C1001	West	1/5/2020	Open	Previous	1
J6	C1001	West	1/6/2020	Call	Current	2
J7	D1001	East	1/1/2020	Open	Previous	1
J8	D1001	East	1/2/2020	Receive	Current	2
J9	F1001	East	1/5/2020	Call	Previous	1
J10	F1001	East	1/7/2020	Receive	Previous	2
J11	F1001	East	1/8/2020	Open	Previous	3
J12	F1001	East	1/9/2020	Call	Previous	4
J13	F1001	East	1/10/2020	Receive	Current	5

So for the above table, my jan count that fits the requirement is 3 (A1001 on 1/3 and F1001 on 1/7 and F1001 on 1/10). 

I tried this DAX, but i keep getting an error with Earlier function. 

Rec_PreVal_Call = calculate(counta(AccHistory[AcctID]), filter (AccHistory, AccHistory[Category] = "East" && AccHistory[KeyVal] = "Receive" ), filter(AccHistory, AccHistory[Category] = "East" && AccHistory[KeyVal] = "Call" && Earlier(Keyval,1)))

Any recommendations on how to achieve this please. 

[Anonymous · Accepted solution]

Hi @PBI5851 ,

Try create a flag as below.

Column = 
var last_date = CALCULATE(MAX('Table'[Start]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]<EARLIER('Table'[Start])))
var last_key = CALCULATE(MAX('Table'[KeyVal]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]=last_date))
return
last_key

Then you should be able to count the id.

Best Regards,

Jay

[OwenAuger]

Hi @PBI5851 

Here's a measure that should work, if I've understood your requirements correctly:

Rec_PreVal_Call = 
SUMX (
    CALCULATETABLE ( 
        SUMMARIZE ( ACCHistory, ACCHistory[AcctiD], ACCHistory[Sequence] ),
        ACCHistory[KeyVal] = "Receive"
    ),
    VAR Seq = ACCHistory[Sequence]
    RETURN
    CALCULATE ( 
        INT ( SELECTEDVALUE ( ACCHistory[KeyVal] ) = "Call" ),
        ACCHistory[Sequence] = Seq - 1
    )
)

There are certainly alternative ways of writing the measure.

I avoided the EARLIER function in this case by using a variable for the current Sequence value (Seq).

Does this work in your model?

Regards,

Owen

[PBI5851]

Owen,
 Thank you for the response. unfortunatly that will not work, as i cannot rely on the sequence field for this calculation, because there is a possibility for other Keyval  values to come into the table and the The Receive row can sometimes be repeated which will negate the seq-1 criteria. 

[OwenAuger]

No problem, we just need some column to determine the order within the formula. Can we use the date in the Start column?