The difference between the earliest two dates

[Anonymous]

Hello, 

I have the table shown, I need a measure (1) that calculates the difference between the earliest two dates

and measure (2) how many times the price changed.

https://docs.google.com/spreadsheets/d/1yDRjc1srQRyoGOkXsubrSJfWDGLtP9U9/edit?usp=sharing&ouid=11016...

your help is highly appreciated.

@Anonymous 

@amitchandak 

[ThxAlot · Accepted solution]

Simple enough,

Cnt = 
CALCULATE(
    COUNTROWS( 'PRICE' ),
    'PRICE'[#]
        <= MAXX(
            INDEX( 2, DISTINCT( 'PRICE' ), ORDERBY( 'PRICE'[Date], DESC ) ),
            'PRICE'[#]
        )
) - 1

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[ThxAlot · Accepted solution]

Simple enough,

Cnt = 
CALCULATE(
    COUNTROWS( 'PRICE' ),
    'PRICE'[#]
        <= MAXX(
            INDEX( 2, DISTINCT( 'PRICE' ), ORDERBY( 'PRICE'[Date], DESC ) ),
            'PRICE'[#]
        )
) - 1

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looksl like but please check the below picture and the attached pbix file.

INDEX function (DAX) - DAX | Microsoft Learn

latest date value vs. second latest date value: =
VAR _t =
    FILTER (
        ALL ( 'Calendar'[Date] ),
        CALCULATE ( SUM ( Data[Price] ) ) <> BLANK ()
    )
VAR _latest =
    CALCULATE (
        SUM ( Data[Price] ),
        INDEX ( 1, _t, ORDERBY ( 'Calendar'[Date], DESC ) )
    )
VAR _second =
    CALCULATE (
        SUM ( Data[Price] ),
        INDEX ( 2, _t, ORDERBY ( 'Calendar'[Date], DESC ) )
    )
RETURN
    IF ( HASONEVALUE ( 'ID'[ID] ), _latest - _second )

OFFSET function (DAX) - DAX | Microsoft Learn

price change count: =
VAR _nonblankdate =
    FILTER (
        VALUES ( 'Calendar'[Date] ),
        CALCULATE ( SUM ( Data[Price] ) ) <> BLANK ()
    )
VAR _t =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Data, 'Calendar'[Date] ),
            "@current", CALCULATE ( SUM ( Data[Price] ) ),
            "@prev",
                CALCULATE (
                    SUM ( Data[Price] ),
                    OFFSET ( -1, _nonblankdate, ORDERBY ( 'Calendar'[Date], ASC ) )
                )
        ),
        [@prev] <> BLANK ()
    )
RETURN
    IF (
        HASONEVALUE ( 'ID'[ID] ),
        COUNTROWS ( FILTER ( _t, [@current] <> [@prev] ) )
    )