Time inteligence DAX

[ManeeshaY_12345]

Hi  I have repeted part numbers with different date, need to create a new column by using dax, that having days numbers, difference between current date and earlier date. if part number is recording for the forst time days should be 0, and for next record  days would be difference between 2nd record date and 1st record date .

ID	Date	No Of Days
R123	23/09/2023	0
R123	22/10/2023	30
R123	01/11/2023	11
R234	29/09/2023	0
R234	12/10/2023	14
R234	23/11/2023	51

[Musadev]

Hi @ManeeshaY_12345 
Please check the steps for your data and update if you are getting the desired results.
Step 1: Here is my custom dataset.

Step 2: I have created a Calculated Column to rank All the products based on the date.

Date Rank =

RANKX (

    FILTER(ALLSELECTED(TI), TI[ID] = EARLIER(TI[ID])),  -- Filter rows for the same ID

    TI[DATE],

    ,

    ASC  -- Sort dates in Ascending order

)
Step 3: Calculate the number of days for each record in the below Calculated column.

# of Days =

VAR MinDate =

    CALCULATE(

        MIN(TI[DATE]),

        FILTER(ALLSELECTED(TI), TI[ID] = EARLIER(TI[ID]))

    )

RETURN

    IF(

        TI[DATE] = MinDate,

        0,  -- Set rank 1 as 0

        DATEDIFF(MinDate, TI[DATE], DAY)  -- Calculate difference in days

    )

Here is the output for my data.

[Musadev]

You will have multiple dates for a single ID right?
right now you have only 3 but in future, it will have multiples

[ManeeshaY_12345]

Yes, In future new records will add for same ID.

[amitchandak]

@ManeeshaY_12345 , We can use offset function to get that

Last row = CALCULATE(Sum(Table[Numer of Days]) , OFFSET(-1, ALLSELECTED('Table'[ID],'Table'[DAte]), ORDERBY('Table'[Date],asc),KEEP,PARTITIONBY('Table'[ID])))+0

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

[ManeeshaY_12345]

Thanks @amitchandak  for sharing above logic,
but I want to confirm you that I am expecting to create NO of days column. as of now I dont have that column in my table for understanding i just mentioned there.
Sorry for unclear information.

[amitchandak]

@ManeeshaY_12345 , My Mistake ,Please try

Last row =
var _last =CALCULATE(Max(Table[Date]) , OFFSET(-1, ALLSELECTED('Table'[ID],'Table'[DAte]), ORDERBY('Table'[Date],asc),KEEP,PARTITIONBY('Table'[ID]))), Max('Table'[Date])
return
if(isblank(_last), 0, Datediff(_last, Max('Table'[Date]) , day))