Previous rank value by customer AND product from last month

[RogerSteinberg]

Hi,

I need to get the previous value by product and customer from the previous month

My data looks likes the following and I need to create a calculated column or measure  ( previous rank value)

date	product	customer	rank_value	previous rank  value
jan	A	X	10
jan	A	Y	20
jan	B	W	30
jan	B	Z	40
feb	A	X	50	10
feb	A	Y	60	20
feb	B	W	70	30
feb	B	Z	80	40

For the sake of simplicity I only put one value column but Ideally I want to replicate this formula for a bunch of other metrics.

I applied this formula and it work for one value metric column but didn't for the others. The formula is identical 

CALCULATE(
    FIRSTNONBLANK('Table'[value_rank],1),
    FILTER(
        'Table',
        'Table'[product]=EARLIER('Table'[product])&&
        'Table'[customer]=EARLIER('Table'[customer]) &&
        'Table'[date]<EARLIER('Table'[date])-1)
        )

 I tried adding the ALL before 'Table' as the first expression of the Filter function but to no avail.

Any ideas what may be causing the issue?

[v-yingjl]

Hi @RogerSteinberg ,

Based on your sample data and your formula, it seems work fine. After I tested in another column by this formula, it is applied normally and got the correct result.

Maybe some reasons that cause it is the Time-intelligence functions firstnoblank(), not certain but try to reduce use Time-intelligence functions because it should be based on a calendar date table and create relationships...

Try to modify like this in your dataset:

Column = 
CALCULATE (
    MAX('Table'[Rank_value]),
    FILTER (
        'Table',
        'Table'[product] = EARLIER ( 'Table'[product] )
            && 'Table'[customer] = EARLIER ( 'Table'[customer] )
            && 'Table'[date]
                < EARLIER ( 'Table'[date] ) - 1
    )
)

If not help, could you please consider sharing a dummy .pbix file for further discussion?

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[harshnathani · Accepted solution]

Hi @RogerSteinberg ,

You can create a Calculated Column

Previous Rank =

Var _previousdate = CALCULATE(MAX('Table'[date]),

FILTER(

'Table',

'Table'[product]=EARLIER('Table'[product])&&

'Table'[customer]=EARLIER('Table'[customer]) &&

'Table'[date] < EARLIER('Table'[date]))

)

RETURN

CALCULATE(MAX('Table'[rank_value]),

FILTER(

'Table',

'Table'[product]=EARLIER('Table'[product])&&

'Table'[customer]=EARLIER('Table'[customer]) &&

'Table'[date] = _previousdate))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)