topic Re: Line Chart visualization based on Avg. Price per year and duration of contract in DAX Commands and Tips

Line Chart visualization based on Avg. Price per year and duration of contract

AVS218 — Wed, 14 Jul 2021 15:32:36 GMT

Hi All,

End Solution Expected: I want to show Avg Price on Line chart for each project based on duration of contract

Fields I have as per below sample table

Project Name	Avg. Price per Year	Total Contract Year
Project a	£ 20000	20
Project b	£ 30000	15
Project c	£ 45000	25
Project d	£ 65000	20
Project e	£ 25000	12
Project n	£ 35000	15

Any idea how to achieve above graphical representation based on these three fields

Re: Line Chart visualization based on Avg. Price per year and duration of contract

lbendlin — Sat, 24 Jul 2021 21:18:58 GMT

Your sample data is missing the date/year column.

Re: Line Chart visualization based on Avg. Price per year and duration of contract

AVS218 — Sun, 25 Jul 2021 05:25:30 GMT

Apologies I missed on that... Data will look like below and I have to consider Contract Start Date as Year 1 and then build on that based on Total Contract Duration. i.e. for Project A Contract Start Year as per date is 2019 and duration is 20 years. so my line chart should display £20k price from 2019 to 2039 and so on for other projects.

Eventually we would like to visualize in two ways one displayed in my previous message and one which will show how much all projects cumulatively earn revenue in a specific year e.g. 2025

Project Name	Avg. Price per Year	Contract Start Date	Total Contract Duration
Project a	£ 20000	20/07/2019	20
Project b	£ 30000	20/01/2020	15
Project c	£ 45000	20/05/2020	25
Project d	£ 65000	25/10/2020	20
Project e	£ 25000	24/02/2021	12
Project n	£ 35000	22/05/2021	15

Re: Line Chart visualization based on Avg. Price per year and duration of contract

mussaenda — Mon, 26 Jul 2021 06:25:54 GMT

Hi AVS218 ,

Based on the sample data you provided,

please see the attached file.

The result is based on the expected output you provided.

Hope this idea helps.

Re: Line Chart visualization based on Avg. Price per year and duration of contract

AVS218 — Mon, 26 Jul 2021 16:37:34 GMT

Super thanks for the solution. Really Appreciate it. It helped sort out main issue and gave us endless possibility

Re: Line Chart visualization based on Avg. Price per year and duration of contract

AVS218 — Fri, 30 Jul 2021 14:58:53 GMT

mussaenda

Thanks a lot... It helped us to solve major challenge we had and kill alternative approach used which was very time consuming. Post implementation of that solution, we came across a scenario where we have some projects with values split by year and some consolidated ... See example below. Is it possible to generate same visualisation when you have some projects captured as one line and some in multiple lines with unique line value

Visualisation we are trying to achieve is one by contract start year and one based on solution you already provided

Project Name	Contract Start Date	Contract End Date	Contract Term (Years)	Avg. Price per year
Project A	31/03/2021	31/03/2026	5	40000
Project B	25/11/2021	24/11/2026	5	42000
Project C	25/04/2023	24/04/2024	20	50000
Project C	24/04/2024	24/04/2025	20	51000
Project C	24/04/2025	24/04/2026	20	60000
Project C	24/04/2026	24/04/2027	20	60000
Project C	24/04/2027	24/04/2028	20	60000
Project C	24/04/2028	24/04/2029	20	60000
Project C	24/04/2029	24/04/2030	20	60000
Project C	24/04/2030	24/04/2031	20	60000
Project C	24/04/2031	24/04/2032	20	60000
Project C	24/04/2032	24/04/2033	20	60000
Project C	24/04/2033	24/04/2034	20	65000
Project C	24/04/2034	24/04/2035	20	65000
Project C	24/04/2035	24/04/2036	20	65000
Project C	24/04/2036	24/04/2037	20	65000
Project C	24/04/2037	24/04/2038	20	65000
Project C	24/04/2038	24/04/2039	20	66000
Project C	24/04/2039	24/04/2040	20	66000
Project C	24/04/2040	24/04/2041	20	67000
Project C	24/04/2041	24/04/2042	20	67000
Project C	24/04/2042	24/04/2043	20	67000
Project D	31/03/2021	31/03/2026	5	53000
Project E	01/12/2021	01/12/2022	20	75000
Project E	01/12/2022	01/12/2023	20	75000
Project E	02/12/2021	01/12/2024	20	75000
Project E	01/12/2024	01/12/2025	20	75000
Project E	03/12/2021	01/12/2026	20	75000
Project E	01/12/2026	01/12/2027	20	75000
Project E	04/12/2021	01/12/2028	20	75000
Project E	01/12/2028	01/12/2029	20	75000
Project E	05/12/2021	01/12/2030	20	75000
Project E	01/12/2030	01/12/2031	20	75000
Project E	06/12/2021	01/12/2032	20	75000
Project E	01/12/2032	01/12/2033	20	75000
Project E	07/12/2021	01/12/2034	20	75000
Project E	01/12/2034	01/12/2035	20	75000
Project E	08/12/2021	01/12/2036	20	75000
Project E	01/12/2036	01/12/2037	20	75000
Project E	09/12/2021	01/12/2038	20	75000
Project E	01/12/2038	01/12/2039	20	75000
Project E	10/12/2021	01/12/2040	20	75000
Project E	01/12/2040	01/12/2041	20	75000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000