Contracts - Various Start/End Dates - Breakdown Value on months

Resolved · Urgent Priority · Version 2016

Wed 3 Feb 2021, 09:19 Delegate 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:01 Trainer 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.pbix
Contracts.xlsx

Thu 11 Feb 2021, 15:58 Delegate 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.

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Scheduled Refresh and Power BI Service

If 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.

View all Power BI hints and tips