Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
5 years ago
Solved

MAX date by group

Hi,    I have below table below,         I am trying to find days between current date and max date by ID. As you can see there are 2 layer below ID. I am expecting this result,   ...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    damit23183 

    Ok, I see what you mean now. Try this:

    Last Date =
CALCULATE (
    LASTDATE ( FactTable[Date] ),
    ALLEXCEPT (
        FactTable,
        FactTable[ID],
        FactTable[Activity],
        FactTable[Sub Activity]
    )
)

    and

    Days from today = INT(TODAY() - [Last Date])

     

     

     

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

Well I'm happy if it helped! Though you lost me with the SQL bits... I know nothing about SQL!!

BTW, if the problem is duplicate rows, can't you just remove them in Power Query?

MichaelLowden's avatar
MichaelLowden
Frequent Visitor
4 years ago

The remove duplicates doesn't seem to do any of what I'd expect. It's like it just keeps the first record and drops all after. But you can't "sort" the data so the first record is actually the one you want (not in a way that I could find anyway). And the SQL-Bits are for 2 reasons, one for myself to translate the DAX into a pattern that I live/breath and to help others like me pivoting business logic into DAX / M-Query.