Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register 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 July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
62 | |
61 | |
55 | |
38 | |
27 |
User | Count |
---|---|
83 | |
61 | |
45 | |
41 | |
39 |