How to calculate the previous date/price before the last date/price

[EZV12]

Hello all,

I need help to figure out a formula to calculate the previous date/price before the last date/price.  Here below please find a matrix for explanantions.  How can I apply a formula for the last 2 columes "The date before the last Start Date" & "Price before latest price"?

Customer	Item	Start Date	End Date	Price	Latest Start Date	Latest Price	The date before the Last Start Date	Price before latest Price
A	Item1	01/01/2019	31/12/2019	5 €
A	Item1	01/01/2020	30/04/2021	6 €			01/01/2020	6 €
A	Item1	01/05/2021	31/12/2021	7 €	01/05/2021	7 €
B	Item1	01/06/2017	31/12/2020	5,50 €			01/06/2017	5,50 €
B	Item1	01/01/2021	31/12/2099	6,50 €	01/01/2021	6,50 €
C	Item2	01/05/2018	31/12/2018	3 €
C	Item2	01/01/2019	30/06/2021	4 €			01/01/2019	4 €
C	Item2	01/07/2021	31/12/2022	5 €	01/04/2021	5 €

Many thanks in advance for your help.

Best regards

Rachel

[v-easonf-msft · Accepted solution]

Hi, @EZV12 

Try formulas as below:

The date before the Last Start Date = 
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Start Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer] = MAX ( 'Table'[Customer] )
                && 'Table'[Item] = MAX ( 'Table'[Item] )
        )
    )
VAR _datebefore =
    CALCULATE (
        MAX ( 'Table'[Start Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer] = MAX ( 'Table'[Customer] )
                && 'Table'[Item] = MAX ( 'Table'[Item] )
                && 'Table'[Start Date] < _date
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Start Date] ) = _datebefore,
        _datebefore,
        BLANK ()
    )

Price before latest Price = 
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Start Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer] = MAX ( 'Table'[Customer] )
                && 'Table'[Item] = MAX ( 'Table'[Item] )
        )
    )
VAR _datebefore =
    CALCULATE (
        MAX ( 'Table'[Start Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer] = MAX ( 'Table'[Customer] )
                && 'Table'[Item] = MAX ( 'Table'[Item] )
                && 'Table'[Start Date] < _date
        )
    )
VAR _price =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER ( 'Table', 'Table'[Start Date] = _datebefore )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Start Date] ) = _datebefore, _price, BLANK () )

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[EZV12]

Hello @v-easonf-msft ,

Thank you very much for your help and sorry for the late reply as I thought nobody could help during several days.  I found out the solution by myself as well.

Many thanks for your time.

Best regards

Rachel

[EZV12]

Hello @v-henryk-mstf @selimovd @v-yiruan-msft ,

May you please help?

Many thanks.

Rachel