Forum Discussion

damit23183's avatar
damit23183
Icon for Microsoft Employee rankMicrosoft 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])

     

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Hi,

To your matrix/table visual, drag the ID column and write these measures

Max date = max(Data[Date])

Difference = today()-[Max date]

Hope this helps.

  • damit23183's avatar
    damit23183
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Hi Ashish,

     

    Thank for your response.

     

    Well, your solution was the first one i tried but due to hierarchy level in table it did not work.

     

    There are multiple level like Level 1 to Level 8 and only one Date column. Further, I need to find MAX date at each level.

     

    Thanks