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

     

     

     

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
    Icon for Microsoft Employee rankMicrosoft 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