Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BlackBird
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
v-yuta-msft
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 @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

 

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

parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.