98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Contracts - Various Start/End Dates - Breakdown Value on months
Contracts - Various Start/End Dates - Breakdown Value on months
Resolved · Urgent Priority · Version 2016
Wed 3 Feb 2021, 09:19Delegate Benjamin said...
Benjamin has attended:
Power BI DAX course
Contracts - Various Start/End Dates - Breakdown Value on months
Dear all,
I am struggling with the following issue.
I have a list with many different Tender contracts. Each contract can contain several products.
Each product/tender combination is represented in a single row. Currently 20.000 and increasing.
These contracts do have different start dates and end dates (DD.MM.YYYY) and values against it.
Many contracts are running for 24 months while others might be running for 3 years, 1 year or only a couple of months.
Instead of now assigning the full tender/contract value to a particular timepoint (eg. The start day) I would like to smoothen the value over the actually run time of a tender. This means a tender with a duration of 24 months and a value of 2400 € shall be shown over 24 months with a value of 100 € for each month.
This logic shall then be applied across all contract lines and be put into a stacked bar chart with a monthly view. For each month I will then be able to see the total (monthly) value of all contracts which are still running in those months.
Example:
Contract – Start – End – Duration – value p. Month
A – 01.01.2021 – 31.03.2021 – 3 - 100 €
B - 01.02.2021 – 31.03.2021 – 2 – 100 €
C - 01.03.2021 – 30.04.2021 – 2 – 100 €
Jan: 100 € (A)
Feb: 200 € (A+B)
March: 300 € (A+B+C)
April: 100 € (C)
So far I have tried the following.
Generated a new Date table. This date table has then been having 2 inactive relationships. One to the start date and one to the end date.
Then I used the following formular.
Tender Value per Month - In Progress =
CALCULATE(
SUM('Data Set' [Value per DAY] ),
FILTER(
VALUES( 'Data Set' [START DATE_] ),
'Data Set' [START DATE_] <= MAX('Date'[Date] ) ) ,
FILTER(
VALUES( 'Data Set' [END DATE] ),
'Data Set' [END DATE] >= MIN('Date'[Date] ) ) )
By using this approach I was able to reflect the value per day. Start and End Date do have the format DD.MM.YYYY. But I am not able to reproduce this for a monthly view of the data set.
I then thought about generate one row/line for each month a contract is valid. Given the number of contracts more than 20.000 this solution seems to be inappropriate.
Hopefully the problem description is understandable.
Pleas let me know if you need any further details and I am looking forward to hearing from you.
Thank you and best regards
Benjamin
Wed 3 Feb 2021, 11:01Trainer Jens said...
RE: Contracts - Various Start/End Dates - Breakdown Value on mon
Hi Benjamin,
Thank you for the forum question.
You have inactive relationships to the start date and end date. This makes this a bit more complicated, but I have a solutions.
In you source table add a Calculated column
AmountPerMonth = [Total value]/(DATEDIFF([Start Date],[End Date],MONTH)+1)
Create New table to get a Date table to use to break down your values per month.
CalendarSplitAmountMonth = FILTER(CALENDAR("2020-01-01","2022-12-31"),DAY([Date])=1)
Create another New table to join the CalendarSplitAmountMonth and your source table
JoinCalendar&Contracts = FILTER(CROSSJOIN(Contracts,'CalendarSplitAmountMonth'),CalendarSplitAmountMonth[Date]>=Contracts[Start Date]&&'CalendarSplitAmountMonth'[Date]<=Contracts[End Date])
If you create the column chart from the JoinCalendar&Contracts table, the Date field and the AmountPerMonth you can get the chart you want.
I will attach my sample file and source data (Excel file), but it may take some minutes before you can see the attachment. If you cannot see the attachments refresh your browser. If you still cannot see them, try again 10 minutes later.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Contracts.xlsxThu 11 Feb 2021, 15:58Delegate Benjamin said...
RE: Contracts - Various Start/End Dates - Breakdown Value on mon
Hi Jens,
Thanks for your support. Solution works perfectly fine.
One Question though please.
I am now able to show the contractual value per month thanks to your solution.
On the same page though I would like to display different visuals with data from the original dataset.
I would like to have one “slicer” to choose the date from to change the graphs with data from the original dataset and also would like this date selection to change the time frame of the “contractual value per month solution”.
Is this possible and if so, how would you do?
Thank you and best regards
Benjamin
Wed 10 Feb 2021: Automatically marked as resolved.
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Scheduled Refresh and Power BI ServiceIf you're using Power BI Service, set up scheduled refresh for your datasets. This ensures that your reports are always up-to-date with the latest data. Understand the refresh limits imposed by Power BI Service and optimize your data model and queries to stay within those constraints. |
