Forum Discussion
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 ...
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.
Here is the output for my data.
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.