How to group table by date

[alexbjorlig]

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 😅

[Jihwan_Kim]

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

[alexbjorlig]

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.

[Fowmy · Accepted solution]

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

)

[alexbjorlig]

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 😎

[Fowmy]

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

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

[amitchandak]

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