MAX date by group

[damit23183]

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

[PaulDBrown]

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

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 · Accepted solution]

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

[MichaelLowden]

OMGosh!!!!!!! I've been fighting Incremental Refreshes over record duplication for "too" long. I can't believe the solution was as easy as a basic "IF MAX() PARTITION OVER() == MAX()" concept. THANK YOU!!!!! You gave me the MAX() PARITION OVER() portion (sorry I use SQL a lot, still learning DAX's oddities / limitations)

[PaulDBrown]

Well I'm happy if it helped! Though you lost me with the SQL bits... I know nothing about SQL!!

BTW, if the problem is duplicate rows, can't you just remove them in Power Query?

[MichaelLowden]

The remove duplicates doesn't seem to do any of what I'd expect. It's like it just keeps the first record and drops all after. But you can't "sort" the data so the first record is actually the one you want (not in a way that I could find anyway). And the SQL-Bits are for 2 reasons, one for myself to translate the DAX into a pattern that I live/breath and to help others like me pivoting business logic into DAX / M-Query.

[damit23183]

Hi Paul,

I got the solution now.

THank you so much for your help. 

Really appreciate it!

[PaulDBrown]

@damit23183 

Great! I was going to say that by creating one single measure, including all the level columns in the ALLEXCEPT expression, you will get the result whether you create individual visuals or a whole matrix including all levels:

[damit23183]

Hi Paul,

Thanks for quick response.

You mean i will have to include all levels ID column in LAST DATE calculated Column which you did mention?

or Individual for each ID so for example in this case ID is level 1 that means in when i calculate LAST DATE then only ID should be there in ALL EXCEPT funtion and so on fo other levels?

[Ashish_Mathur]

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]

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

[v-luwang-msft]

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]

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

[damit23183]

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.

[v-luwang-msft]

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]

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

[Anonymous]

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]

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