Visualise different dates of 3 categories over time

[synaptical]

Hi community

I try to visualise (I thought of an area chart/line chart) various dates over a timeline which are related to one column (Offer Type) consisting of three entries/categories (Small, Medium, Large).

The goal is to visualise, in which months we had or will have peaks due to new offers, due submissions and when we expect the orders to fly in. A monthly scale visualsation is sufficient (we currently do not need to be more granular).

Table: Offers

Offer Type	Offer Start	Offer Submission	Estimated Order
Small	15.01.2024	28.01.2024	15.02.2024
Medium	20.01.2024	10.02.2024	20.02.2024
Large	15.02.2024	15.04.2024	30.06.2024
Medium	01.03.2024	01.06.2024	15.07.2024
Large	10.03.2024	15.06.2024	30.08.2024

I also have created another table (Calendar) which contains all the dates - starting from earlist Offer Start date and end with latest Estimated Order date. There are currently no relations between them.

Calendar = CALENDAR(MIN('Offers'[Offer Start]),MAX('Offers'[Estimated Order]))

How could I achieve such a visualisation? Taking the example above, we would have a peak in March 2024 (Medium and Large starting) because they start more or less together. Also in June 2024, as there are two submissions - means we would be limited in starting new offers then.

Any help is kindly appreciated.

[danextian · Accepted solution]

Hi @synaptical 

Right click Offer Type in the query editor and select Unpivot Other Columns which should move the names and the values of the other column into their own columns

Alternatively, you can in create an inactive relationships with two of the date columns. The broken lines in the screenshot below are for an inactive relationship as there can only be one active relationship between two tables.

Then use USERELATIONSHIP to invoke such a relationship. Please see the attached pbix.

[synaptical]

Hi @danextian 

Thank you so much for your help and the .pbix file. I was able to understand and learn the concept behind. And thanks for the measures in NotPivoted - without them I wouldn't understand the big pic. I was then able to create my needed line chart showing the progression of my three dates.

Thank you again for your solution and help.

[danextian]

My pleasure to have helped.

[Ashish_Mathur]

Hi,

the very first thing you should do is right click on the first column and select "Unpivot Other Columns".  Next, create a relationship (Many to One and Single) from the Value column to the Date column of the Calendar table.

[synaptical]

Hi, thanks for your reply. I opened the edit query and selected unpivot columns (column Offer Type). Now I have in every column just "error" - I get the data for the Offers table by connecting to a SharePoint list.

[Ashish_Mathur]

You are doing something wrong.  Please retry. 

[synaptical]

Thank you. This is exceeding my beginner skills in Power BI. Also I would have to recreate the whole dashboard, as in the real project I have of course much more columns, like customer name, responsible people etc. I thought of an approach using measures or something.