Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

The difference between the earliest two dates

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=110166684500340294500&rtpof=true&sd=true

 

your help is highly appreciated.

 

Anonymous 

amitchandak 

Need Help

3 Replies

  • ThxAlot's avatar
    ThxAlot
    Super User
    2 years ago

    Simple enough,

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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] ) )
    )