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

 

 

 

MichaelLowden's avatar
MichaelLowden
Frequent Visitor
4 years ago

OMGosh!!!!!!! I've been fighting Incremental Refreshes over record duplication for "too" long. I can't believe the solution was as easy as a basic "IF MAX() PARTITION OVER() == MAX()" concept. THANK YOU!!!!! You gave me the MAX() PARITION OVER() portion (sorry I use SQL a lot, still learning DAX's oddities / limitations)

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