Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Time inteligence DAX

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 .

IDDateNo Of Days
R12323/09/20230
R12322/10/202330
R12301/11/202311
R23429/09/20230
R23412/10/202314
R23423/11/202351
Data Protection
General Comment
Interesting Links
Show and Tell
Tips and Tricks
Tutorial Requests

6 Replies

  • Musadev's avatar
    Musadev
    Resolver III
    2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

      • amitchandak's avatar
        amitchandak
        Super User
        2 years ago

        Anonymous , 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

  • Musadev's avatar
    Musadev
    Resolver III
    2 years ago

    Hi Anonymous 
    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.