how to ignore a date from a fact table in a matrix ?

[Susanne]

I have made a matrix that shows correct results, untill I throw in a date field.

I have 2 facts and 2 dims:

fact orders		fact mutations
amount ordered		amount delivered
date expected		date delivered
dim date (to both facts 1-*)		dim order (to both facts 1-*)
		ordernumber

I need to bring de amount ordered and amount delivered in 1 matrix (with month field) What happends is the amount are shown on 2 rows, which is logic:

supplier A			amount ordered	amount delivered
	month
		May	20
		June		20

in month May 20 items were ordered

In month June 20 items were delivered

I need to show them in one line, where de date expected is leading en de data delivered is not important. How do I do that?

[Susanne · Accepted solution]

I am sorry my example wasnt clear.

They will not show the amount in 1 line. Because if the order was made in January, and the delivery in february, de order will be shown with January en the delivery will be shown in February. Reason is both tables have their own relationship tot dim_date. 

I tried to calculate OTIF (On Time and In Full). 

I found my own solution by adding tot last deliverydate to my fact_orders. That way I can use 1 table only and the problem was solved

[v-yanjiang-msft]

Hi @Susanne ,

Sorry it's hard to reproduce your problem, I'm not very clear about the logical. By my understanding, if you put the month column in the Columns of matrix, and Amount Ordered and Amount Delivered in the Values of matrix, they will show in one line.

Your sample attached is a bit messy, if you can't attach a file, you can show them in a Excel. It will help to solve your problem if you could provide more details.

Thanks!

Best Regards,
Community Support Team _ kalyj