cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User IV
Super User IV

@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






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

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





Community Support
Community Support

@BlackBird ,

 

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

 

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

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

 

https://drive.google.com/drive/folders/1JAQK1lUVxvIefVxlAYScfEtmlWGs8gTk 

 

@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.

@BlackBird ,

 

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

 

Regards,

Jimmy Tao

@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.

@BlackBird ,

 

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

https://drive.google.com/drive/folders/1JAQK1lUVxvIefVxlAYScfEtmlWGs8gTk 

 

@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.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors