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 ，

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