Rankx within group combined with dateadd

[Anonymous]

Hello everyone,

I found Rankx can be completed if used as a measure. Any help will be greatly appreciated! 

I have built a Date table and one to many relationship with the Test Table on the “Date” Column ( Please see the screenshot below).

I would like to achieve the following:

Date	Link Code	Volumes	PM Volumes	Rank	PM Rank
01/04/2021	A	18		3	1
01/04/2021	B	62		1	1
01/04/2021	C	19		2	1
01/05/2021	A	12	19	3	2
01/05/2021	B	103	12	1	3
01/05/2021	C	15	103	2	1
01/06/2021	A	33	15	1	3
01/06/2021	B	18	33	3	1
01/06/2021	C	29	18	2	2

However, this is what returned with my Dax below

Date	Link Code	Volumes	PM Volumes	Rank	PM Rank
01/04/2021	A	18		3	1
01/04/2021	B	62		1	1
01/04/2021	C	19		2	1
01/05/2021	A	12	19	3	1
01/05/2021	B	103	12	1	1
01/05/2021	C	15	103	2	1
01/06/2021	A	33	15	1	1
01/06/2021	B	18	33	3	1
01/06/2021	C	29	18	2	1

Rank =

RANKX(filter(all('Test table'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),CALCULATE(sum('Test table'[ Volumes])),,DESC)

PM Volumnes = CALCULATE(sum('Test table'[ Volumes]),dateadd('Dim Date 2'[Date],-1,month))

PM rank =

rankx(filter(all('Test table'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),[PM Volumnes],,desc)

May I ask where I did wrong? Why PM Rank doesn’t return as expected?

Thank you.

Jing

[CNENFRNL · Accepted solution]

RANKX() is one of caviars of DAX; but also is tremendously tricky one in spite that it's very commonly used.

PM Rank = 
VAR __pm = DATEADD( DATES[Date], -1, MONTH )
RETURN
    IF(
        NOT ISEMPTY( CALCULATETABLE( TEST, __pm ) ),
        RANKX(
            SUMMARIZE(
                CALCULATETABLE( TEST, __pm, ALLSELECTED( TEST[Link Code] ) ),
                TEST[Link Code],
                DATES[yyyy-MMM]
            ),
            [Total Vol],
            CALCULATE( [Total Vol], __pm )
        )
    )

[Anonymous]

@CNENFRNL  Thank you very much for your help! It works. I can't say I fully understand the codes yet but I think I will understand it better while I am geting more advanced in Dax. Many thanks! 

[amitchandak]

@Anonymous , Please date frmpm date table in Rank and visual

rankx(filter(all('Date'[Date],'Test table'[Link Code]),'Date'[Date]=max('Date'[Date])),[PM Volumnes],,desc)