Calculating Ago Metric From Fact and Dimension derived column

User_IP06I

Hello All,

I have two tables. Table A is a dimension tables although it has Metrics in it (Aggregated table). It is Joined to Fact Table B on key1, key2, key3. Then a logical column is created using columns from two tables. A case statement is written in such a way that it will calculate "metric XX"  as  (sum(quantity)/Metric1) or (sum(quantity)/Metric2) or (sum(quantity)/Metric3). Another time dimension is joined to Fact table B. Using that "Metric XX previous month" (Ago("Business Model"."Table B"."Metric XX" ,  "Business model"."Time Dimension"."Month" , 1).

when Month from time dimension and the Metric XX from table B is pulled Into the analysis it's working fine but when Metric XX previous month is added the results are not as expected. They are duplicating as in the pic below. Please take a look and let me know if you know why it is not returning the correct values.

PS: I calculated the Metric XX previous month in the analysis then also it's not working. I tried calculating the previous month calculation using ago for Sum(Quantity) column, it's working fine. So I'm thinking the metric XX is not returning the Expected values because Table A is not joined with time dimension. Correct me if I'm wrong.

Table A:

Key1	Key2	Key3	Metric1	Metric2	Metric3
123	A	AAA	1	2	3
234	B	BBB	2	3	4
345	C	CCC	3	4	5

Table B:

Key1	Key2	Key3	Sum (Quantity)
123	A	AAA	10
234	B	BBB	20
345	C	CCC	30

Robert Angel

Hi,

can you provide a picture showing your physical and logical table joins.

Also can you explain if your measure then you are putting time series on is physical or logical and ditto for the elements involved in its calculation.

Finally can you show what the content levels are set to for the logical table sources involved.