Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
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.
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.
@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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |