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/s...
Need Help
  • ThxAlot's avatar
    ThxAlot
    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] ) )
    )