Forum Discussion

alexbjorlig's avatar
alexbjorlig
Helper IV
4 years ago
Solved

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...
DAX
Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    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)

)

     



Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

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's avatar
alexbjorlig
Helper IV
4 years ago

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.