DAX Create column LAST status based on MAX date by Id

[Anonymous]

Hi,

I am having trouble with an already discussed topic in here but I have a slightly different version. My data structure is the following one: 

id	value	Date entered
1	y	10/12/2018
1	x	10/13/2018
2	x	10/12/2018
2	z	10/13/2018
2	y	10/16/2018

I want to add one new column which should have the latest Value per ID on each row. The solution should work when I apply the explicit filter on the Date entered.

I got the following working code already (This calculates me the max Date) :

max date = 
var mind=MINX(ALLSELECTED('calendar'),'calendar'[Date]) 
var maxd=MAXX(ALLSELECTED('calendar'),'calendar'[Date])   
return CALCULATE(MAX('Table 2'[dateentered]), FILTER(ALL('Table 2'), 'Table 2'[id]=MIN('Table 2'[id]) && 'Table 2'[dateentered]>=mind && 'Table 2'[dateentered]<=maxd))

 Now I want to return the latest vale of the max Date. Therefore I got the following code but the Lookupvalue expression doesn't work because I have duplicates in the date entered column. Can someone please help me which expression I should use instead.

max value = LOOKUPVALUE('Table 2'[value],'Table 2'[dateentered],[max date])

 The result should be the following one:

id	value	date entered	Latest Date	Latest Value
1	y	10/12/2018	10/13/2018	X
1	x	10/13/2018	10/13/2018	X
2	x	10/12/2018	10/16/2018	y
2	z	10/13/2018	10/16/2018	y
2	y	10/16/2018	10/16/2018	y

Thank you very much

[richbenmintz · Accepted solution]

Hi @Anonymous,

Assuming you data Model looks like this

The following Measure provides the expected results

Date Latest Value = 
var datesSelected = VALUES('Calendar'[Date])
var tid = VALUES('Table'[id])
var maxDate = CALCULATE(MAX('Table'[Date entered]),ALL('Table'), 'Table'[Date entered] in datesSelected, 'Table'[id] in tid)
var maxValue = CALCULATE(max('Table'[value]), FILTER(ALL('Table'), 'Table'[Date entered] = maxDate && 'Table'[id] in tid))
return maxValue

pbix file here, sample.pbix 

I Hope this helps,

Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[mahoneypat]

Please try this expression

Latest Value Measure =
VAR latestdate =
    CALCULATE (
        MAX ( Latest[date entered] ),
        ALL ( Latest ),
        VALUES ( Latest[id] )
    )
RETURN
    CALCULATE (
        MIN ( Latest[value] ),
        ALL ( Latest ),
        VALUES ( Latest[id] ),
        Latest[date entered] = latestdate
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@Anonymous , Try

lastnonblankvalue(Table[Date entered], max(Table[value]))

The option are use it with all except with ID

calculate(lastnonblankvalue(Table[Date entered], max(Table[value])),allexcept(Table[ID]))

[Greg_Deckler]

@Anonymous - Use MAXX or MINX instead of LOOKUPVALUE. Basically this is the Lookup Min/Max pattern found here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434