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,

 

 

 

 

 

So here No of days is difference between current date and MAX date from each ID.

 

Thanks

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

     

     

     

18 Replies

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

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi damit23183 

    Create a Column using DAX below.

    No Of Days = DATEDIFF(Calculate(MAX('table'[date]),ALLEXCEPT('table','table'[date])),TODAY(),DAY)

OR

No Of Days = DATEDIFF(Calculate(MAX('table'[date]),ALLEXCEPT('table','table'[date])),'table'[Current Date],DAY)

    Add +1 at the end if you want to include todays date too.

    • damit23183's avatar
      damit23183
      Microsoft Employee
      5 years ago

      Hi Pranit,

      Thank you for your response.

       

      I already tried this solution before but it gave me different result. 

      For example, ID no 2 has total 2 entries(If you see Activity and Sub activity) correct.

      So, when i tried to apply this formula it gave me no of days  = 22  (11x2).

      I only want to show it 11 and thats it.

      Is there other solution?

       

      Thanks

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    HI damit23183 

    You could use the following measures:

    maxactday = FORMAT(CALCULATE(MAX('Table'[Date].[Date]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID]))),"YYYY/MM/DD")

    No Of Days = DATEDIFF('Table'[maxactday],NOW(),DAY)

     

    Then you will get what you want,see the below:

     

    Click  here  to download pbix if you need.

    Best Regard

    Lucien Wang

     

    • damit23183's avatar
      damit23183
      Microsoft Employee
      5 years ago

      Hi,

      Thanks for your response.

      I tried this too but its not working either.

      Its giving total days that means in your case if ID "1" has 3 entry so I am getting 45 days in No of Days column when i applied your formula.

       

      Thanks

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    HI damit23183 ，

    Have you tried as following steps:

    Step 1, use the following measure to get the max date for every id :

    Measure dax :maxactday = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])))

     

     

    Step 2, use the following measure to find days between current date and maxactday:

    Measure dax :No Of Days = DATEDIFF('Table'[maxactday],NOW(),DAY)

     

    If you have tried the above steps and  it still no work, can you provide me with your pbix file？Remember to remove the confidential information.

     

    Best Regard

    Lucien Wang

     

    • damit23183's avatar
      damit23183
      Microsoft Employee
      5 years ago

      Hi,

      THanks for checking in and sorry for late response as i got stuck with other work.

      I will try this too and let you know but your previous solution work 50%. 

      So, let me try and see this solution.

      On PBIX i cant share the data as its confidential. I am really sorry for that.

    • damit23183's avatar
      damit23183
      Microsoft Employee
      5 years ago

      Hi,

      WHen i was trying to use this and your previous solution, just found out that Measure is not working when i tried to find days between columns. It always come with memory not enough error message whenever i used measure to find DATEDIFF.

      Is there any specific reason why?

       

      THanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super 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
      Microsoft 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