- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
111 | |
77 | |
54 | |
53 | |
44 |
User | Count |
---|---|
183 | |
119 | |
77 | |
65 | |
56 |