Applying rank based on narrowed date

[wek9294]

Hello, I'm trying to get my table to show the correct ranking based on a narrowed date field. All of the data is from one table.

My data has these columns:

Lane: abc - def

Cost

Date column: using the formula 

If(Datediff('table'[Date],TODAY(),WEEK)=13, "Yes","no")

Lane based on date column: using the formula

If('table'[Date column] = "Yes", 'table'[Lane], BLANK())

Narrowed Cost: using the formula

If(Datediff('table'[Date],TODAY(),WEEK)=13, 'table'[Cost], blank())

I'm trying to get the rank for the "Lane based on date column" using either the "Cost" or "Narrowed Cost" column.  Basically I want it to look at lanes that appeared 13 weeks ago, sum the costs, then apply a rank to those summed costs.

[amitchandak]

@wek9294 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

For Rank Refer these links
https://www.youtube.com/watch?v=wDS_Vi4r9I4
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[wek9294]

Does this help? Here's what my raw data looks like. Note it doesn't have a "Week #" column but I added it so it's easier to know how I'm narrowing the data.

Here's what I need to get. For the report, say current date is 1/5/2020, meaning we're in week 2.

It's only adding together the values from the previous week. Note that the dates in the report will consistantly change so it can't focus on a specific week number, but instead has to look at one week prior to current week.

So when it's 1/12/20 and we're in week 3, I need to get

[v-yiruan-msft · Accepted solution]

Hi @wek9294 ,

I created a sample pbix file(see attachment) base on your provided data, please check whether that is what you want.

1. Create a Date table(Note: Do not create any relationship between Date table and your fact table)

2. Create the below measures

Measure = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _selweek =
    WEEKNUM ( _seldate )
RETURN
    CALCULATE (
        SUM ( 'Table'[Cost] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Lane] = MAX ( 'Table'[Lane] )
                && 'Table'[Week #] < _selweek
        )
    )

New cost = SUMX(VALUES('Table'[Lane]),[Measure])

Rank = 
IF (
    ISBLANK ( [New cost] ),
    BLANK (),
    RANKX ( ALLSELECTED ( 'Table' ), [New cost],, DESC, DENSE )
)

Best Regards