Split Contract Value based on 30 days calculation

Hi,

Our understanding is that the user wants to display a monthly breakdown of contract values based on a consistent split of 30 days, regardless of the actual days in the month. Below is the DAX formula that addresses this requirement:

Monthly Breakdown =

VAR CurrentDate = SELECTEDVALUE('Calendar'[Date]) 

VAR ContractStartDate = MAX(Contracts_Details[Start Date]) 

VAR ContractEndDate = MAX(Contracts_Details[End Date]) 

VAR TotalContractValue = MAX(Contracts_Details[Total Contract Value]) 

// Calculate Daily Value assuming a 30-day month for all months

VAR DailyValue = DIVIDE(TotalContractValue, 30)

VAR StartOfMonth = EOMONTH(CurrentDate, -1) + 1

VAR EndOfMonth = EOMONTH(CurrentDate, 0)

// Determine the overlap between the contract period and the current month

VAR OverlapStart = MAX(ContractStartDate, StartOfMonth)

VAR OverlapEnd = MIN(ContractEndDate, EndOfMonth)

// Calculate the total overlap days within the current month

VAR OverlapDays = DATEDIFF(OverlapStart, OverlapEnd, DAY) + 1

// Calculate the monthly amount based on the overlap

VAR MonthlyAmount =

    SWITCH(

       TRUE(),

        OverlapDays <= 0, 0,  // No overlap

        OverlapDays = 30, DailyValue * 30,  // Full month covered by the contract

        DailyValue * OverlapDays  // Partial month covered by the contract

    )

RETURN

    MonthlyAmount

If you still don’t get the expected result, please share your PBIX and Excel files so that we can further investigate the issue.

Hope this Helps.