Display Date of Last non blank Actual Value

[myou]

Hello,

I have been struggling to build this measure

I have this Table 

I want to show the Date of the last non blank Actual Value, only for category = ABC

Note: Date should be less than today

So Team ID=1 

The last non blank for Actual is 100, so I want to read the corresponding date for it

Date	Actual	teamid	categ
1/1/2020	20	1	ABC
2/1/2020	100	1	ABC
3/1/2020		1	ABC
4/1/2020		1	ABC
1/1/2020	100	2	XYZ
2/1/2020	200	2	XYZ
3/1/2020	300	2	XYZ
4/1/2020	400	2	XYZ
1/1/2020	100	3	ABC
2/1/2020	100	3	ABC
3/1/2020	100	3	ABC
4/1/2020		3	ABC

Desired Output

Teamid	Date
1	2/1/2020
3	3/1/2020

Trial:

DueDateLastFilledActual = 
var F1= Filter(Table2,Table2[Date] <=today())
return
CALCULATE(
    
    max(Table2[Date]),
    LASTNONBLANK(Table2[Actual],1)
)

[vanessafvg]

hi, give this a try @myou 

you need to create a new table 

Table 2 =
SUMMARIZE (
    FILTER (
        'Table',
        'Table'[categ] = "ABC"
            && LASTNONBLANK ( 'Table'[Date], 'Table'[Actual] )
    ),
    'Table'[teamid],
    "New Date", MAX ( 'Table'[Date] ),
    "Actual", SUM ( 'Table'[Actual] )
)

[camargos88 · Accepted solution]

Hi @myou ,

Try this code:

CALCULATE(MAX('Table'[Date]); LASTNONBLANK('Table'[Actual]; 1); 'Table'[categ] = "ABC"; 'Table'[Date] <= TODAY())

I hope it helps,

Ricardo

[myou]

Hi @camargos88 

It shoud show March 1 for teamid2, the measure return BlankShould show March1 for teamid 2

[camargos88]

Hi @myou ,

TeamId 2 doesn't have ABC category, right ?

Also, I got this returned:

TeamId     Date

1               02/01/2020

3               03/01/2020

[Greg_Deckler]

@myou I am having some difficulty with the logic. Is the logic

1. Find the last non blank Actual value for all teams (latest date)

2. Lookup that value for each team and get the latest date but only for teams that have that value as their latest value (latest date)

I ask because I see that team 2 is not included in your results.

[myou]

Hi @Greg_Deckler ,
I want to include only category ABC, thats why teamid 2 isnt included

For the Logic, 

1. Find the last non blank Actual value for all teams (latest date)

2. Lookup that value for each team and get the corresponding date