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

     

     

     

v-luwang-msft's avatar
v-luwang-msft
Icon for Community Support rankCommunity 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
    Icon for Microsoft Employee rankMicrosoft 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