topic Re: How to group table by date in DAX Commands and Tips

How to group table by date

alexbjorlig — Tue, 28 Sep 2021 13:59:22 GMT

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 😅

Re: How to group table by date

amitchandak — Tue, 28 Sep 2021 14:06:31 GMT

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

Re: How to group table by date

Fowmy — Tue, 28 Sep 2021 14:18:45 GMT

alexbjorlig

Try this table, it will work for any number of columns in your table

New Table = FILTER( table, VAR __cat = Table[category ] RETURN Table[date] = CALCULATE( MAX(Table[date]) , REMOVEFILTERS() , Table[category ] = __cat) )

Re: How to group table by date

alexbjorlig — Tue, 28 Sep 2021 14:25:34 GMT

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 😎

Re: How to group table by date

Fowmy — Tue, 28 Sep 2021 14:29:08 GMT

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.

Re: How to group table by date

Jihwan_Kim — Tue, 28 Sep 2021 14:33:49 GMT

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
newtableconnect

Re: How to group table by date

alexbjorlig — Tue, 28 Sep 2021 14:34:12 GMT

Amazing.

So they key difference to understand for me here, is the "filter" context vs row context. The REMOVEFILTERS only clears the row context, so any filters there would still "work"?

Re: How to group table by date

alexbjorlig — Tue, 28 Sep 2021 14:36:50 GMT

Whaaat - 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.