Split Contract Value based on 30 days calculation

[shareezsaleem]

Hi Team,

I need a solution on the below issue.

I have a table consist of contract reference no., start date, end date and total contract value.

I need to visualize the monthly values (my start date starts in 2016) in a chart/table.

I have used the below formula to get the contract value breakdown:

VAR StartRange=FIRSTDATE('Calendar'[Date])
VAR LastRange=LASTDATE('Calendar'[Date])
VAR RevisedStart=MAX(StartRange,MIN(Contracts_Details[Start Date]))
VAR RevisedEnd=Min(LastRange,Max(Contracts_Details[End Date]))
VAR Result= 1*(RevisedEnd-RevisedStart+1)
VAR DV=MAX(Contracts_Details[Daily Value])
VAR Result2=MAX(0,Result)*DV
RETURN
Result2

FYI, Calendar table is not connected with the fact table.

Below is the excel calculation and it's screenshot.

Daily value calculation=D2/MAX(30,DAYS360(B2,C2+1))

Monthly breakdown calculation= =$E2*
IF(AND(MONTH(F$1)=MONTH($B2),YEAR(F$1)=YEAR($B2),MONTH($C2)=MONTH(F$1),YEAR(F$1)=YEAR($C2)),30,
IF(AND(MONTH(F$1)=MONTH($B2),YEAR(F$1)=YEAR($B2)),DAYS360($B2,F$1+1),
IF(AND(MONTH(F$1)=MONTH($C2),YEAR(F$1)=YEAR($C2)),DAYS360(EOMONTH(F$1,-1)+1,$C2+1),
IF(OR(EOMONTH(F$1,-1)+1>$C2,F$1<$B2),0,MAX(30,DAYS360(EOMONTH(F$1,-1)+1,EOMONTH(F$1,0)))))))

Table showing in Power BI:

I wanted to get the same split amount in of Excel in Power BI also.

Can you please help me to write a DAX measure?

[shareezsaleem]

Awaiting reply.

@SamInogic @amitchandak 

[shareezsaleem]

Awaiting reply.

[SamInogic]

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.

[shareezsaleem]

I have used the below formula and getting right amount split.

But the problem is Total is showing wrong.

Monthly Value Calculation:

DV Days360 = Contracts_Details[Value]/MAX(1,YEARFRAC(Contracts_Details[Start Date],Contracts_Details[End Date]+1,0)*30*12)

Amount Split Calculation:
Contract Value Breakdown = VAR HeadEndDate=EOMONTH(LASTDATE('Calendar'[Date]),0) VAR RevisedStart=MIN(Contracts_Details[Start Date]) VAR RevisedEnd=Max(Contracts_Details[End Date]) VAR DaysCount=IF(AND(MONTH(HeadEndDate)=MONTH(RevisedStart),YEAR(HeadEndDate)=YEAR(RevisedStart)) && AND(MONTH(RevisedEnd)=MONTH(HeadEndDate),YEAR(HeadEndDate)=YEAR(RevisedEnd)),30, IF(AND(MONTH(HeadEndDate)=MONTH(RevisedStart),YEAR(HeadEndDate)=YEAR(RevisedStart)),YEARFRAC(RevisedStart,HeadEndDate+1)*360, IF(AND(MONTH(HeadEndDate)=MONTH(RevisedEnd),YEAR(HeadEndDate)=YEAR(RevisedEnd)),YEARFRAC(EOMONTH(HeadEndDate,-1)+1,RevisedEnd+1)*360,IF(OR(EOMONTH(HeadEndDate,-1)+1>RevisedEnd,HeadEndDate<RevisedStart),0,MAX(30,YEARFRAC(EOMONTH(HeadEndDate,-1)+1,EOMONTH(HeadEndDate,0))))))) RETURN

DaysCount*MAX(Contracts_Details[DV Days360])

Total Value (Total is wrong both in Table & Card visuals)

Contract Value = SUMX(Contracts_Details,[Contract Value Breakdown])

[amitchandak]

@shareezsaleem , refer if these approaches can help

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2