Forum Discussion
How to group table by date
I'm new to DAX, and need to find the latest value by date in a measure.
Input:
| category | date | value1 | value2 |
| A | 3/3/2020 04:30:00 | 1 | 0 |
| A | 2/1/2020 04:30:00 | 0 | 1 |
| A | 1/1/2020 04:30:00 | 0 | 1 |
| B | 1/1/2020 04:30:00 | 1 | 1 |
| B | 2/2/2020 06:30:00 | 0 | 0 |
| C | 1/1/2020 04:30:00 | 1 | 0 |
Desired output:
| category | date | value1 | value2 |
| A | 3/3/2020 04:30:00 | 1 | 0 |
| B | 2/2/2020 06:30:00 | 0 | 0 |
| C | 1/1/2020 04:30:00 | 1 | 0 |
What I have tried:
GROUPBY(Table, Table[category], "Latest", MAXX(CURRENTGROUP(), Table[date]))
But this "only" gives me:
| category | date |
| A | 3/3/2020 04:30:00 |
| B | 2/2/2020 06:30:00 |
| C | 1/1/2020 04:30:00 |
I feel like I should be able to use a filter function, but did not succeed in making it work 🙃
Thanks in advance for any help 😅
alexbjorlig
Try this table, it will work for any number of columns in your tableNew Table = FILTER( table, VAR __cat = Table[category ] RETURN Table[date] = CALCULATE( MAX(Table[date]) , REMOVEFILTERS() , Table[category ] = __cat) )
7 Replies
alexbjorlig
Try this table, it will work for any number of columns in your tableNew Table = FILTER( table, VAR __cat = Table[category ] RETURN Table[date] = CALCULATE( MAX(Table[date]) , REMOVEFILTERS() , Table[category ] = __cat) )I know it'sprobably super hard to explain, but why does this work - I'm so impressed! 😂
The CALCULATE uses the REMOVEFILTERS() --> but I guess that could be avoided, or what does that exactly do?
And the __cat variable is a refrence to the filter context, making sure it's only done for the "correct" category?So simple, so elegant 😎
alexbjorlig
With the Filter function, there is a row context, you only one record at a time, REMOVEFILTERS clears the row context filter and shows all the records and again _cat filters by category obtained from the row context. Now the max date is taken and matched.
alexbjorlig , Based on what I got. In a table visual Take Max of value 1 and Min of value 2
Or create a new table
summarize(Table, Table[Category], Table[Date], "Val1", Max(Table[Value1]), "Val2", Min(Table[Value2]))
Hi,
If you want to use groupby function, please try the below.
New Table =
VAR newtable =
GROUPBY (
'Table',
'Table'[category],
"Latest Date", MAXX ( CURRENTGROUP (), 'Table'[date] )
)
VAR newtableconnect =
CALCULATETABLE (
'Table',
TREATAS ( newtable, 'Table'[category], 'Table'[date] )
)
RETURN
newtableconnectWhaaat - that is also working and another interesting solution. Would there be any differnce to the results compared to Fowmy - or is more of a prefrence thing?
I'm already using GroupBy to a bunch of my measures, so I kind of like the idea, but comparing the number of lines written, the other solution is more compact.
