Date Difference of current record and its relative record using MDX

SSAS and SSRS
1

Dear All,

I have a some rows where it points to another row in same table based on RelativeId column value. I need to find the difference of these two records date values using MDX.

for ex. sample table structure with some values

ID - HasRelative - Date - RelativeId

1 - False - 2023-01-01 - NULL

2 - True - 2023-08-01 - 1

Here record 2 has a relative record 1. The output should be difference of Date values between 2 records which are mapped with RelativeId.

Thank you very much

2

Hi.
Assuming the ID field is the primary key of the table, I come up with a solution like this:

create table #T
(
	ID INT
	, HASRELATIVE BIT
	, [DATE] DATE
	, RELATIVEID INT
)
INSERT INTO #T (ID, HASRELATIVE, [DATE], RELATIVEID) VALUES
	(1, 0, '2023-01-01', NULL)
	, (2, 1, '2023-08-01', 1)

; WITH CTE AS
(
	SELECT
		*
		, 
		(
			CASE WHEN HASRELATIVE = 1 THEN
				DATEDIFF(DAY, (SELECT [DATE] FROM #T T2 WHERE T2.ID=T1.RELATIVEID), T1.[DATE])
			ELSE
				NULL
			END
		) AS RELATIVE_DATEDIFF
	FROM #T T1
)
SELECT * FROM CTE
DROP TABLE #T

But it seems too simple to me. Did I really understand what you need?
:slight_smile:

3

Hi gdl, thanks for the reply.

I need it using SSAS MDX query.

Thanks.

4

Sorry satya, I missed SSAS MDX.
I can't help with that.