Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table that has a start date of the contract starting from March 2017 and ending at April 2018. I want to calculate the yearly contract price. See the table below.
Contract Name | Start Date of Contract | End Date of Contract | Price |
X | 1/3/2017 | 31/3/2018 | 387 |
X | 1/4/2018 | 31/3/2019 | 387 |
X | 1/4/2019 | 31/12/2020 | 250 |
I want to calculate what was the annual price for each year and for all the contracts. How do I do this?
HI @Kolumam ,
Let me know if you are lookin for this. If not, pls provide the expected o/p.
You can try this measure.
Annual Contract Price =
VAR diff_stdat_endate =
DATEDIFF (
MAX ( 'Table'[Start Date of Contract] ),
MAX ( 'Table'[End Date of Contract] ),
MONTH
)
RETURN
DIVIDE (
12,
diff_stdat_endate
)
* MAX ( 'Table'[Price] )
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Thanks but I need to plot the values using a Year column. How do I do this for each contract?
@Kolumam , can share expected output.
Current Contract = CALCULATE(Maxx(FILTER(Contract,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Contract[End Date]) || Contract[End Date]>max('Date'[Date]))),(Contract[Price])),CROSSFILTER(Contract[Start Date],'Date'[Date],None))
If you create a date table and join it with the start date above will give max price for the year. Year should be there in Date table
As per the formula given by @harshnathani , I tried it but the issue is the date. When I filter the year to 2020, it should ideally show the "Annual Contract Price" as (450*7/12) which is 262.5. How do I add a proper date axis and filter it with year?
@Kolumam , Just see if this blog can help
You will get a monthly amount
Or this file can help
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
Hi @amitchandak
The solution looks very complex.
I am looking for a yearly contract price calculation for each contract.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |