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.