Forum Discussion

shareezsaleem's avatar
shareezsaleem
Helper III
1 year ago

Split Contract Value based on 30 days calculation

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?

Need Help

5 Replies

  • SamInogic's avatar
    SamInogic
    Super User
    1 year ago

    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's avatar
      shareezsaleem
      Helper III
      1 year ago

      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])