creating a matrix with rows as order month and column as delivery month

[Maggi]

Hi,

I have a dataset where I have a order date and delivery date. When using Excel pivotTable I can create a matrix were I see how orders in each month spread over the year. Order month is displayed in rows and delivery date in column. 

For example, I want to see how orders in January spread over the year. Let´s say I have 50 orders and i want to know in what month each should be delivered. If 10 of the 50 orders are being delivered sometime I February I want that to be the value in February.  I am in the travel industry and usually the orders are made far in advance. 

Is there anyway I can achieve this with Power BI?

I have not been able to connect the two dates with a matrix. I am using userrelationship function to activate eace date I am using. 

Here you can see the build up of the Matrix:

Thanks in advance.

[v-kelly-msft]

Hi  @Maggi ,

Can you show me your sample data if it's not confidential?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Syndicate_Admin]

Hi Kelly,

Thank you for the reply.

Unfortunately the data is confidential..

Regards,

Magnus

[Maggi]

Hi Kelly,

Thank you for the reply.

Unfortunately the data is confidential..

Regards,

Magnus

[v-kelly-msft]

Hi @Maggi ，

Can you make some sample data and expected output,not your actual data just sample...I cant reproduce your senario simply by your pic showed in original post.

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Maggi]

Hi @v-kelly-msft,

I have created a sample data with order date and delivery date. These two dates should be the only data needed for creating the table.

Here you can find the sample data: https://bluecarrental-my.sharepoint.com/:u:/g/personal/magnusthor_bluecarrental_is/EbvbQsrJiptNk8IfL...

As I am new to this I hope this is sufficient for you 🙂

Thank you for taking your time for this 🙂

The output should look like this:

[v-kelly-msft]

Hi  @Maggi ,

First delete the relationship between Fact table and calendar;

Then create a matrix and make a setting as below:

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Maggi]

Hi @v-kelly-msft 

Thank you very much for your help and your time!

It works when I delete the relationships. The problem is that i will need the relationship for measures in other tables. 

Is it not possible to keep the connection for other visulizations?

Thank you 🙂

Regards,

Magnus

[v-kelly-msft · Accepted solution]

Hi @Maggi ，

If you wanna keep the relationship,you need to create 4 calculated columns first.(the relationship will remove the hierarchy of the fact table,so you need to get the month and year for order date and delivery date,see below)

Month_order = FORMAT('Fact table'[Order date],"mmmm")

Month_delivery = FORMAT('Fact table'[Delivery date],"mmmm")

Year_order = FORMAT('Fact table'[Order date],"yyyy")

Year_delivery = FORMAT('Fact table'[Delivery date],"yyyy")

Then create 2 measures as below:

Measure = CALCULATE(COUNTROWS('Fact table'),FILTER(ALL('Fact table'),'Fact table'[Month_delivery]=MAX('Fact table'[Month_delivery])&&'Fact table'[Month_order]=MAX('Fact table'[Month_order])))

Measure 2 = SUMX(SUMMARIZE('Fact table','Fact table'[Month_order],'Fact table'[Month_delivery]),'Fact table'[Measure])

Finally you will see:

Here is the modified .pbix file you can refer to.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Should be possible, would need to see the data, how it is connected. Might need disconnected month tables (2) and just use a measure.

[Maggi]

Hi Greg,

Thank you for the reply.

Here are the connection I have made with the data:

The order date and delivery date connected to the calander.

If you need anything else let me know.

Thank you!

Magnus