Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Applying rank based on narrowed date

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    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

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

        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