cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DInamic Line Chart of Sadness

Hi!

I’ve almost exhausted my mind trying to figure this out, and based on what I already found I think what I’m trying to do it’s not possible, but I have hope you guys can point me the right direction.

Oh, and sorry for any typos, I’m still learning English.

I have a table with data from Contracts and another table with the Monthly Payments.

The tables looks like this:

Contracts:

 Contract Number Budget Date of Start Date of End 570003215 259.684.777,00 01/12/2016 01/12/2022 570003216 32.569.874,00 01/12/2018 01/12/2029 570003217 65.468.798,00 01/12/2018 01/12/2021

MonthlyPayments:

 Contract Number Reference Value 570003215 jan-19 2.365.987,00 570003216 jan-19 325.698,00 570003217 jan-19 321.654,00 570003215 fev-19 365.987,00 570003216 fev-19 3.216.548,00 570003217 fev-19 321.656,00 570003215 mar-19 1.365.987,00 570003216 mar-19 665.988,00 570003217 mar-19 6.521.485,00 570003215 abr-19 3.365.987,00 570003216 abr-19 321.556,00 570003217 abr-19 3.215.488,00

So, based on this tables, I do some calculations to use on a line chart:

IdealMonthlyPayment = Budget / (Date of End – Date of Start) * in months

AcumulatedMonthlyPayment = Sum of MonthlyPayments[Value] across the months

AcumulatedIdealMonthlyPayment = Sum of IdealMonthlyPayment across the months

BudgetEndTendency = Date of Last Monthly Payment + (Budget Leftover / Average of the Last 3 Monthly Payments)

The BudgetEndTendency measure will return the probable date when the budget will be entirely used.

I can show BudgetEndTendency on a Card or a Table, it works fine, but my manager wants to see it on the Line Chart. And here the problem starts.

The X-axis of the Line Chart must be dynamic, starting on the Contract[Date of Start] to the BudgetEndTendency or the Contract[Date of End], whichever ends later, and I have no clue on how to do this.

What you guys think?

9 REPLIES 9
Highlighted
Super User IV

## Re: DInamic Line Chart of Sadness

@BlackBird I think the best is to put your sample data in excel and show what you want expected output, it will help to povide the solution

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Community Support

## Re: DInamic Line Chart of Sadness

I'm afraid we couldn't use measure as X-axis in line chart, you may try slicer/visual level filter instead.

Regards,

Jimmy Tao

Highlighted
Frequent Visitor

## Re: DInamic Line Chart of Sadness

Hi @parry2k, thanks for answering, I'll do it.

Highlighted
Frequent Visitor

## Re: DInamic Line Chart of Sadness

Hi @v-yuta-msft , I did some research and found this. With the slicers you mean to change the entire X-axis right? Like one for Products and one for Categories?

That's not what I'm looking for... what I need to do is to create a table, based on the Start and End dates of each contract, here is a example:

Contract1 / Start= 01/01/2019 / End= 01/05/2019

Contract2 / Start=01/03/2019 / End=01/08/2019

The table should look like this:

Contract1 | January/19

Contract1 | February/19

Contract1 | March/19

Contract1 | April/19

Contract1 | May/19

Contract2 | March/19

Contract2 | April/19

Contract2 | May/19

Contract2 | June/19

Contract2 | July/19

Contract2 | August/19

Highlighted
Frequent Visitor

## Re: DInamic Line Chart of Sadness

@parry2kit took me more time than I though. But here it is, I put the Data and the Chart I'm trying to make.

Maybe there is another aproach to this? Calculating a 60k+ rows table to one chart only bugs me, it doesnt seem right.

Highlighted
Community Support

## Re: DInamic Line Chart of Sadness

Where does the Contract1 / Start and Contract1 / End come from? Could you please clarify the logic?

Regards,

Jimmy Tao

Highlighted
Frequent Visitor

## Re: DInamic Line Chart of Sadness

@v-yuta-msft, sure.

These contracts, usually to provide services or rent equipment/machines, have all details like start and end dates, budget, scope, etc, defined on closure. But they can be altered while the contract is still active, like more budget or a longer/shorter end date.

I'm sorry if I'm not using the right terminology here, but I think you will understand. The dates/budget are defined acording to demand.

Highlighted
Community Support

## Re: DInamic Line Chart of Sadness

In the original table I haven't found any values which contains Contract1/Contract2. Please share the complete sample data so that I could do further analysis on it.

Regards,

Jimmy Tao

Highlighted
Frequent Visitor

## Re: DInamic Line Chart of Sadness

@v-yuta-msftplease use the link above to download the file, it has the tables and the chart. Currently I'm using this excel file to arrange the data the way I need, and it's working fine.

By the way, @parry2k thanks for sugesting making the excel file to explain things better, I'm actually using it now and the manager liked the result.

But still can't do it directly on pbi.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors