Most recent calculation

[Anonymous]

Good day,

Hope somebody can help me with the following.

I have below table.

If I want to calculate the most recent Imut_dat of the partnumber_sup, I use below calculation (calculated column).

But how will the calculation look like if I want to have the most recent Imut_dat for every warehouse?

If I have this info, I can judge for every warehouse if a part is dead stock in that particular warehouse.

Thanks!

most recent Imut_dat=
var thispartnr = 'war_mut (2)'[partnumber_sup]
var lastDateByPartnr =
CALCULATE(
MAX('war_mut (2)'[lmut_dat] )
;FILTER(
ALL('war_mut (2)')
;'war_mut (2)'[partnumber_sup] = thispartnr
)
)
return
IF('war_mut (2)'[lmut_dat] = lastDateByPartnr; "yes"; "no")

warehouse	partnumber_sup	lmut_dat
212	120043306RP	5-9-2017 08:40:03
212	120043306RP	11-11-2017 10:26:09
212	120043306RP	8-12-2017 11:30:05
214	120043306RP	15-12-2017 20:52:22
214	120043306RP	29-12-2017 14:34:03
212	120043306RP	2-1-2018 15:18:06
212	120043306RP	26-1-2018 18:56:07
212	120043306RP	28-2-2019 15:04:05
212	120043306RP	22-7-2019 17:16:05
212	120043306RP	2-10-2019 12:05:02
212	120049502SV	2-9-2017 08:38:04
212	120049502SV	4-9-2017 14:16:05
212	120049502SV	20-9-2017 15:02:05
212	120049502SV	30-9-2017 11:32:07
212	120049502SV	9-10-2017 09:26:10
212	120049502SV	25-10-2017 16:30:04
212	120049502SV	14-11-2017 09:34:05
214	120049502SV	21-11-2017 20:56:06
212	120049502SV	27-11-2017 13:02:06
212	120049502SV	5-12-2017 14:18:03
214	120049502SV	12-12-2017 19:28:13
214	120049502SV	14-12-2017 18:04:03
212	120049502SV	18-12-2017 11:14:03
212	120049502SV	18-12-2017 11:34:05

[amitchandak · Accepted solution]

@Anonymous , try like

Measure =LASTNONBLANKVALUE(Table[partnumber_sup],max(Table[lmut_dat]))
or
Measure = maxx(SUMMARIZE(Table,Table[warehouse],"_1",LASTNONBLANKVALUE(Table[partnumber_sup],max(Table[lmut_dat]))),[_1])

[Anonymous]

@AM 

Thanks for your help, looks good!

[nandukrishnavs]

@Anonymous 

I didn't understand your requirement clearly. Anyway, I have tried a few calculated columns.

Lastdate of warehouse = MAXX(FILTER(ALL('war_mut (2)'),'war_mut (2)'[warehouse]=EARLIER('war_mut (2)'[warehouse])),'war_mut (2)'[lmut_dat])

This will give you the last date in every warehouse.

Lastdate of warehouse and partnumber = MAXX(FILTER(ALL('war_mut (2)'),'war_mut (2)'[warehouse]=EARLIER('war_mut (2)'[warehouse])&&'war_mut (2)'[partnumber_sup]=EARLIER('war_mut (2)'[partnumber_sup])),'war_mut (2)'[lmut_dat])

This will give the last date of warehouse and partnumber combination.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂