Max for date and time 2 columns

[patoku0207]

Hello, 

First,sorry for my english, I'm french.

This forum is incredible and very helpful.

I'm new and I need help for a calculated column.

I'm trying to find the value for a max date and time with filters.

Suffix time is a incremented number when date and time are similars.

So the very last date and time is date, max time , max suffix.

Item	number	date	time	suffix time	balance
1	12345	20220131	2953	1	1100
1	12346	20220131	3578	1	1100
1	999999999	20220201	153854	1	1500
1	12347	20220131	2953	1	1127
2	12347	20220131	4752	1	3544
2	999999999	20220131	45624	1	3748
2	45787	20220131	45624	2	3750
2	999999999	20220201	75886	2	5000

Resultat : 

Item	number	date	time	suffix time	balance
1	12345	20220131	2953	1
1	12346	20220131	3578	1
1	999999999	20220201	153854	1
1	12347	20220131	2953	1	1127
2	12347	20220131	4752	1
2	999999999	20220131	45624	1
2	45787	20220131	45624	2	3750
2	999999999	20220201	75886	2

Im trying to find the balance for each item, each date, max time and max suffix time, excluding number = 99999999.

Thanks for your help

Pat

[patoku0207]

Hello,

It wont let me validate the measure

Thanks for help.

Pat

[patoku0207]

Hello tomfox,

Thanks for your help, this solved my issue 

Congrats

Pat

[tackytechtom · Accepted solution]

Hi @patoku0207 ,

Just so I understand correctly, you would like to return the balance per item and number which has the highest date. If two dates are equal, then we shall take the higher time. If even these two times are equal, we shall take the higher time suffix. For the calculation we must not consider the rows with number = 999999999.

This is my result:

This is the measure I used:

TomsBalanceMeasure = 
VAR _maxValue = 
CALCULATE (
    MAXX ( Table, Value( Table[date] & Table[time] & Table[suffix time] ) ),
    ALLEXCEPT ( Table, Table[Item] ),
    Table22[number] <> 999999999
)
RETURN
CALCULATE ( 
  MAX ( Table[balance] ), 
    VALUE ( Table[date] & Table[time] & Table[suffix time] ) == _maxValue  
)

Does this solve your issue? 🙂

/Tom

https://www.tackytech.blog/

https://www.instagram.com/tackytechtom/

[patoku0207]

Hello,

If its too hard, I just want to find the latest date for each date for each item.( ignore the number filter).

The latest date is a combinaison of date, max time, max suffix

Ex :

Item 1 : 20220131   3578 is the max time   1 is the max suffix

Item 2 : 20220131   45624 is the max time   2 is the max suffix

Thanks in advance for your help.

Pat