Forum Discussion
MAX date by group
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])
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
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])
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?
Hi Paul,
I got the solution now.
THank you so much for your help.
Really appreciate it!
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:
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)
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?
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.
