Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I'm trying to create a simple column chart as follow
with 2 tables: Calendar Date and Contract table.
I have 2 date relationships between both tables, by using Date column in Calendar Date table with one active relationship with Contract Active Date and another inactive one with Contract End Date, from Contract table.
For this visual, I need to use Contract End Date in the DAX formula.
And I'd like to use Month column from Calendar Date, for easier interaction with other visuals.
But it's not as expected.
When I'm using Month from Contract End Date, the visual is correct though.
This is the DAX I used. I'm not sure which one is wrong, and hope to get help.
Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi @lbendlin
Thanks for the reminder.
Here's the data of source table.
Customer | Annual Contract Value | Active Date | Contract Start Date | Contract End Date |
Cust A | 40,000 | 31-Dec-19 | 1-Mar-18 | 28-Feb-23 |
Cust A | 50,000 | 31-Dec-20 | 1-Mar-18 | 28-Feb-23 |
Cust A | 55,000 | 31-Dec-21 | 1-Mar-18 | 28-Feb-23 |
Cust A | 60,000 | 31-Dec-22 | 1-Mar-18 | 28-Feb-23 |
Cust A | 75,000 | 28-Feb-23 | 1-Mar-18 | 28-Feb-23 |
Cust B | 75,000 | 31-Dec-19 | 1-Jun-19 | 31-May-22 |
Cust B | 75,000 | 31-Dec-20 | 1-Jun-19 | 31-May-22 |
Cust B | 50,000 | 31-Dec-21 | 1-Jun-19 | 31-May-22 |
Cust B | 50,000 | 31-May-22 | 1-Jun-19 | 31-May-22 |
Cust C | 10,000 | 31-Dec-21 | 1-May-21 | 30-Apr-23 |
Cust C | 15,000 | 31-Dec-22 | 1-May-21 | 30-Apr-23 |
Cust C | 15,000 | 30-Apr-23 | 1-May-21 | 30-Apr-23 |
Cust D | 25,000 | 31-Dec-22 | 1-Sep-22 | 31-Aug-24 |
Cust D | 50,000 | 31-Dec-23 | 1-Sep-22 | 31-Aug-24 |
Cust D | 50,000 | 31-Aug-24 | 1-Sep-22 | 31-Aug-24 |
And the result expected in table view
Month | Value of Contract Expected to be Ended |
2023-02 | 75000 |
2023-04 | 15000 |
2024-08 | 50000 |
What is the importance of Contract Start Date? Is it part of the issue?
how are you amortizing the annual contract value over the months? Or is that not important?
Is this what you are looking for?
Hi @lbendlin
Contract Start Date is not the issue now, and there's no need to amortize contract over the year.
Is your visual using the period from Calendar Table?
Hi @lbendlin
As addition, I'd like to show the contract value for the year of current production year and moving forward. So, previous year, i.e. 2022 is not required.
So, that's why I'm adding a filter:
Please show the expected outcome based on the sample data you provided.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
90 | |
83 | |
76 | |
64 |
User | Count |
---|---|
142 | |
112 | |
98 | |
98 | |
94 |