Distribute value between start and end date, including ending sum

[thomma]

Hi all,

I have a date table and a contract table with contract including a contract amount and start- and end date. I'm trying to distribute the contract amount between a start and end date. I do this by calculating the amount to distribute per day (total/amount of days between start and end). Then I use something like the following DAX code to join the contract amount per day on a date table: Table = FILTER(CROSSJOIN(contracts,DimDates),contracts[StartDate]<='DimDates'[Date]&&contracts[EndDate]>='DimDates'[Date])

Problem is, I also want to model an ending sum of 3% of the total amount on 3 months after the ending date, ideally in the same crossjoin table. I don't know how to do this, anyone who can help me out?

Thanks in advance!

[rajendraongole1 · Accepted solution]

Hi @thomma -To get the contract amount over the duration of the contract and then add an additional 3%.

Create a calculated column with 3% of distribution daily amount

DailyAmount =
DIVIDE(
Contracts[ContractAmount],
DATEDIFF(Contracts[StartDate], Contracts[EndDate], DAY)
)

AdditionalAmount = Contracts[ContractAmount] * 0.03
AdditionalDate = EDATE(Contracts[EndDate], 3)

Create a new table that crossjoins the Contracts table with the Dimension Date table and includes the additional 3% amount:

ContractDistributions =
VAR CrossJoinTable =
FILTER(
CROSSJOIN(Contracts, DimDates),
(DimDates[Date] >= Contracts[StartDate] && DimDates[Date] <= Contracts[EndDate])
|| DimDates[Date] = Contracts[AdditionalDate]
)

VAR AddColumnsTable =
ADDCOLUMNS(
CrossJoinTable,
"DistributedAmount",
IF(
DimDates[Date] = Contracts[AdditionalDate],
Contracts[AdditionalAmount],
Contracts[DailyAmount]
)
)
RETURN
AddColumnsTable

add ContractDistributions table and use it in your report and see the distribution amount across contract period.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!