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  

Try:

max date by ID = CALCULATE(LASTDATE(Table [Date], ALLEXCEPT(Table, Table [ID]))

Today - max date by ID = INT(TODAY() - [max date by ID])

  • damit23183's avatar
    damit23183
    Microsoft Employee
    5 years ago

    Hi Paul,

     

    Thanks for your response but its not working. Let me explain here again what exactly I have.

     

    Here is the table I do have currently (this is an example as i can't share data due to privacy);

     

     

     

     

     

     

    As you can see above there are multiple layer here up to 3 but in original table there 8 levels.

    Now here i would like to calculate no of days between Current date and max of Date column at each level.

     

    so for example, for level 1 which at ID column I would like to see the result like this;

     

     

     

     

    Now same goes to Level 2 which is ACTIVITY column in this example so the result should be like this;

    And for Level 3 which Sub-Activity in this example should look like this;

     

     

     

     

    However, at lowest 3-4 levels i am getting what is expected but at top level i am getting like this;

    Here you can see days are multiplied depending on how many sub activities there.

    So at top level its not working but at low level its working.

     

    Thanks

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

       

       

       

      • damit23183's avatar
        damit23183
        Microsoft Employee
        5 years ago

        Hi Paul,

         

        Thanks for quick response.

        You mean i will have to include all levels ID column in LAST DATE calculated Column which you did mention?

        or Individual for each ID so for example in this case ID is level 1 that means in when i calculate LAST DATE then only ID should be there in ALL EXCEPT funtion and so on fo other levels?