Forum Discussion
Max for date and time 2 columns
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
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
4 Replies
- tackytechtom
Most Valuable Professional
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
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
Hello tomfox,
Thanks for your help, this solved my issue
Congrats
Pat
Hello,
It wont let me validate the measure
Thanks for help.
Pat
