Forum Discussion

thomma's avatar
thomma
Frequent Visitor
2 years ago
Solved

Distribute value between start and end date, including ending sum

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!

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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!!

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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!!