Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fab_incher
Frequent Visitor

RANKX for Rolling last 12 Month

Hi all,

 

I have some daily Data ('Implied Volatility', but only for working days) and i'm struggling to build a measure that gives me a rolling-1-year-Rank measure. If i dont select any Date(s) for row (e.g. in a card), it should show me the current Rank of last 1 year from today on.

 

Probably it is something like...

Rank = RANKX (

                   CALCULATETABLE(...),

                   [newColumnImpliedVolatility]

              )

 

Thanks for your help!

 

Implied Volatility Measure = CALCULATE(AVERAGE(Fact_UnderlyingDaily[ImpliedVolatility]))

 

Bild1.png

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @fab_incher 

try like:

Ranking =
VAR _date = MAX(TableName[Date])
RETURN
RANKX(
    CALCULATETABLE(
        ALLSELECTED(TableName[Date]),
        TableName[Date]<=_date,
        TableName[Date]>=EDATE(_date, -12)
     ),
    [Implied Volatility]
)

View solution in original post

2 REPLIES 2
fab_incher
Frequent Visitor

Hi @FreemanZ  thank you for your solution.

 

One Question: why do we need ALLSELECTED?

FreemanZ
Super User
Super User

hi @fab_incher 

try like:

Ranking =
VAR _date = MAX(TableName[Date])
RETURN
RANKX(
    CALCULATETABLE(
        ALLSELECTED(TableName[Date]),
        TableName[Date]<=_date,
        TableName[Date]>=EDATE(_date, -12)
     ),
    [Implied Volatility]
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors