RUNNING PRICE INCREASE FILTER FIRSTDATE LASTDATE

[MichelleQLi]

Dear Experts,

I have a table "Supplier Rate" including below columns:

"Merchant" - Name of the merchant.
"SKU" - Product code of each item/ each product.
"Category" - Category of items/products.
...
"Description" - Detail description of each item.
"Rate" - Unit price of each item.
"Date Captured" - showing starting date of the quote (the date we got the price). Currently we have:
     - 2022/Feb/01
     - 2022/Mar/05
     - 2022/May/11
"Date Valid" - showing expiring date of the quote.

I need some measure to show:

1) Rate increase by time slicer

I will have a slicer on the page view, which is the "Date Captured".
When I select "2022/Mar/05", I need to show the rate increase from "2022/Feb/01" to "2022/Mar/05".
When I select "2022/May/11", I need to show the rate increase from "2022/Feb/01" to "2022/May/11".
==============================
I tried to use "FIRSTDATE" and "LASTDATE", together with FILTER but doesn't work.

Price Increase (%) =

VAR __BASELINE_VALUE = CALCULATE(SUM('Supplier Rate'[Rate]), FILTER('Supplier Rate', 'Supplier Rate'[Date Captured] = FIRSTDATE('Supplier Rate'[Date Captured])))
VAR __VALUE_TO_COMPARE = CALCULATE(SUM('Supplier Rate'[Rate]), FILTER('Supplier Rate', 'Supplier Rate'[Date Captured] = LASTDATE('Supplier Rate'[Date Captured])))

RETURN
IF(
NOT ISBLANK(__VALUE_TO_COMPARE),
DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
)

[v-henryk-mstf · Accepted solution]

Hi @MichelleQLi ,

For the formula you provided, there seems to be an error. the FIRSTDATE/LASTDATE function returns a table and performs a contextual conversion. It should not be written in this form 'Supplier Rate'[Date Captured] = LASTDATE('Supplier Rate'[Date Captured])

try below formula:

Price Increase (%) =
VAR __BASELINE_VALUE =
    CALCULATE (
        SUM ( 'Supplier Rate'[Rate] ),
        FILTER ( 'Supplier Rate', FIRSTDATE ( 'Supplier Rate'[Date Captured] ) )
    )
VAR __VALUE_TO_COMPARE =
    CALCULATE (
        SUM ( 'Supplier Rate'[Rate] ),
        FILTER ( 'Supplier Rate', LASTDATE ( 'Supplier Rate'[Date Captured] ) )
    )
RETURN
    IF (
        NOT ISBLANK ( __VALUE_TO_COMPARE ),
        DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE )
    )

or use MAX/MIN instead LASTDATE:

Price Increase (%) =
VAR __BASELINE_VALUE =
    CALCULATE (
        SUM ( 'Supplier Rate'[Rate] ),
        FILTER (
            'Supplier Rate',
            'Supplier Rate'[Date Captured] = MAX ( 'Supplier Rate'[Date Captured] )
        )
    )
VAR __VALUE_TO_COMPARE =
    CALCULATE (
        SUM ( 'Supplier Rate'[Rate] ),
        FILTER (
            'Supplier Rate',
            'Supplier Rate'[Date Captured] = MAX ( 'Supplier Rate'[Date Captured] )
        )
    )
RETURN
    IF (
        NOT ISBLANK ( __VALUE_TO_COMPARE ),
        DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE )
    )

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

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