Compare value between two dates

[gjloureiro]

I need two compare two value between two date.

I have a list with customers, product, price and sell date, and i need to know when the price by customer was readjusted.

[Zubair_Muhammad · Accepted solution]

Hi @gjloureiro

Try these MEASURES

Actual Price =
CALCULATE (
    VALUES ( TableName[PRICE] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[CLIENT] ),
        TableName[DATE] = MAX ( TableName[DATE] )
    )
)

Date of Actual Price =
CALCULATE (
    LASTDATE ( TableName[DATE] ),
    ALLEXCEPT ( TableName, TableName[CLIENT] )
)

[Zubair_Muhammad · Accepted solution]

@gjloureiro

Following MEASURES for the previous/last DATE and VALUE

lastdate =
VAR last_price = [Actual Price]
RETURN
    CALCULATE (
        MAX ( TableName[DATE] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[CLIENT] ),
            TableName[Actual Price] <> last_price
        )
    )

lastvalue =
VAR priordate = [LastDate]
RETURN
    CALCULATE (
        VALUES ( TableName[PRICE] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[CLIENT] ),
            TableName[DATE] = priordate
        )
    )

[v-shex-msft · Accepted solution]

Hi @gjloureiro,

You can try to use below table formula to get the analysed table.

Original table:

Formula:

Table = 
ADDCOLUMNS (
    SUMMARIZE (
        'Sample',
        [CLIENT],
        [ITEM],
        "Min", MIN ( 'Sample'[PRICE] ),
        "Max", MAX ( 'Sample'[PRICE] )
    ),
    "Last Min Price Date", MAXX (
        FILTER (
            'Sample',
            [CLIENT] = EARLIER ( [CLIENT] )
                && [ITEM] = EARLIER ( [ITEM] )
                && [PRICE] = EARLIER ( [Min] )
        ),
        [DATE]
    ),
    "Last Max Price Date", MAXX (
        FILTER (
            'Sample',
            [CLIENT] = EARLIER ( [CLIENT] )
                && [ITEM] = EARLIER ( [ITEM] )
                && [PRICE] = EARLIER ( [Max] )
        ),
        [DATE]
    )
)

 

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi @gjloureiro,

You may refer to my solution in this workbook.

Hope this helps.

[Zubair_Muhammad · Accepted solution]

Hi @gjloureiro

Try these MEASURES

Actual Price =
CALCULATE (
    VALUES ( TableName[PRICE] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[CLIENT] ),
        TableName[DATE] = MAX ( TableName[DATE] )
    )
)

Date of Actual Price =
CALCULATE (
    LASTDATE ( TableName[DATE] ),
    ALLEXCEPT ( TableName, TableName[CLIENT] )
)

[Zubair_Muhammad · Accepted solution]

@gjloureiro

Following MEASURES for the previous/last DATE and VALUE

lastdate =
VAR last_price = [Actual Price]
RETURN
    CALCULATE (
        MAX ( TableName[DATE] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[CLIENT] ),
            TableName[Actual Price] <> last_price
        )
    )

lastvalue =
VAR priordate = [LastDate]
RETURN
    CALCULATE (
        VALUES ( TableName[PRICE] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[CLIENT] ),
            TableName[DATE] = priordate
        )
    )

[Zubair_Muhammad]

@gjloureiro

See the Excel file attached here as well