Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi, I have a table of contracts with start and end dates and need to plot on a graph the number of contracts that end or start in a given month. Any ideas?
Solved! Go to Solution.
Hi @Anonymous,
Taking into account that you have two date columns first of all I would create a calendar table (tutorial calendar table).
1 - Create a two non-active relationships between the Calendar table and your Start and End Date:
2 - Add the following measures:
Start_Date_Count = CALCULATE ( COUNT ( Contracts[Contract] ), USERELATIONSHIP ( 'Calendar'[Date], Contracts[Start_Date] ) ) End_date_Count = CALCULATE ( COUNT ( Contracts[Contract] ), USERELATIONSHIP ( 'Calendar'[Date], Contracts[End_Date] ) )
If need only one measure create the following measure:
Total_Date_Count = CALCULATE ( COUNT ( Contracts[Contract] ), USERELATIONSHIP ( 'Calendar'[Date], Contracts[Start_Date] ) ) + CALCULATE ( COUNT ( Contracts[Contract] ), USERELATIONSHIP ( 'Calendar'[Date], Contracts[End_Date] ) )
3 - Create your visuals with the desired measure.
See below a sample with my table, the visuals with start and end month calculation and another with the measures I created to see the result is the same, with the Calendar table you can create slicers that will allow you to show only the information you want.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous,
Taking into account that you have two date columns first of all I would create a calendar table (tutorial calendar table).
1 - Create a two non-active relationships between the Calendar table and your Start and End Date:
2 - Add the following measures:
Start_Date_Count = CALCULATE ( COUNT ( Contracts[Contract] ), USERELATIONSHIP ( 'Calendar'[Date], Contracts[Start_Date] ) ) End_date_Count = CALCULATE ( COUNT ( Contracts[Contract] ), USERELATIONSHIP ( 'Calendar'[Date], Contracts[End_Date] ) )
If need only one measure create the following measure:
Total_Date_Count = CALCULATE ( COUNT ( Contracts[Contract] ), USERELATIONSHIP ( 'Calendar'[Date], Contracts[Start_Date] ) ) + CALCULATE ( COUNT ( Contracts[Contract] ), USERELATIONSHIP ( 'Calendar'[Date], Contracts[End_Date] ) )
3 - Create your visuals with the desired measure.
See below a sample with my table, the visuals with start and end month calculation and another with the measures I created to see the result is the same, with the Calendar table you can create slicers that will allow you to show only the information you want.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks so much. Works a charm. I then tried to use a waterfall charter with a measure of Runners difference = [Starters]-[Leavers]. This works but doesn't filter by User/Sales person. Any ideas?
Hi @Anonymous,
How does the User/Sales person connects to this measures? Is it on a separeted table or on the same table?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
76 | |
63 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
61 | |
60 |