Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Most recent calculation

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")

 

warehousepartnumber_suplmut_dat
212120043306RP5-9-2017 08:40:03
212120043306RP11-11-2017 10:26:09
212120043306RP8-12-2017 11:30:05
214120043306RP15-12-2017 20:52:22
214120043306RP29-12-2017 14:34:03
212120043306RP2-1-2018 15:18:06
212120043306RP26-1-2018 18:56:07
212120043306RP28-2-2019 15:04:05
212120043306RP22-7-2019 17:16:05
212120043306RP2-10-2019 12:05:02
212120049502SV2-9-2017 08:38:04
212120049502SV4-9-2017 14:16:05
212120049502SV20-9-2017 15:02:05
212120049502SV30-9-2017 11:32:07
212120049502SV9-10-2017 09:26:10
212120049502SV25-10-2017 16:30:04
212120049502SV14-11-2017 09:34:05
214120049502SV21-11-2017 20:56:06
212120049502SV27-11-2017 13:02:06
212120049502SV5-12-2017 14:18:03
214120049502SV12-12-2017 19:28:13
214120049502SV14-12-2017 18:04:03
212120049502SV18-12-2017 11:14:03
212120049502SV18-12-2017 11:34:05
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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])

3 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    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     🙂

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      AM 

      Thanks for your help, looks good!