Forum Discussion
Helper IIICalculate contract duration based on a date slicer
Hi all,
I'm struggling with a measure to get an overview per year of the SUM of the contract duration. this first year should be based on a date slicer.
the content is as follow (simple example):
| contract code | contract end date |
| 1 | 31/12/2022 |
| 2 | 31/12/2023 |
| 3 | 31/12/2022 |
| 4 | 31/12/2025 |
| 5 | 31/12/2021 |
when i set the date slicer on 2020, the result should be:
| 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | |
| 3 | 2 | 1 | 0 | 0 | 0 | |
| 4 | 3 | 2 | 1 | 0 | 0 | |
| 3 | 2 | 1 | 0 | 0 | 0 | |
| 6 | 5 | 4 | 3 | 2 | 1 | |
| 2 | 1 | 0 | 0 | 0 | 0 | |
| SUM contract duration | 18 | 13 | 8 | 4 | 2 | 1 |
I can't figure out what kind of measure I should use in order to get this result. Hopefully somebody can help me out!
3 Replies
- amitchandak
Super User
joep78 , Try a new measure like
Assume you have date calendar
Measure = calculate(datediff(min(Table[contract end date]), startofyear(Date[Date]),Month),all(date)) or Measure = calculate(datediff(min(Table[contract end date]), endofyear(Date[Date]),Month),all(date))You can replace all(date) with allselected(date) or remove as per need
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.- parry2k
joep78 hey see attached, I hope this is what you are looking for. a lot of moving parts in it.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
joep78Thanks for this solution in the example, it is exactly what I was looking for!
Kudos for you!
