Forum Discussion
Responsive ResidentNeed help with YTD measures.
Hi All,
I have a Projects (ABC001) whose where half of data (Jul 2024 to 21 Feb 2025)is in old system and from (22 Feb 2025 to project ends) the data will be in "NewSystem" .
How can I combine the two data to give the total value for YTD - Billed (Ex- GST)
Please suggest.
YTD - Billed(Ex-GST) TEST =
VAR _maxDate = MAX(vFactBudgetActuals[Transaction Date])
VAR OldSystemRevenue =
CALCULATE(
[Revenue Amount] - SUM(vFactBudgetActuals[Cost Amount]),
vFactBudgetActuals[Service ID] = "REVENUE",
DATESYTD('Date'[Date], "6/30") -- Apply Year-to-Date filter
)
VAR NewSystemRevenue =
CALCULATE(
SUM(PJC[TOTAMTSR]),
DATESYTD('Date'[Date], "6/30"),
PJC[CATEGORY] = "REVENUE"
)
RETURN
OldSystemRevenue + NewSystemRevenue
rajendraongole1 please kindly review the measure. Thanks
ashmitp869 you have similar post, check reply on the another post.
2 Replies
- parry2k
Super User
ashmitp869 you have similar post, check reply on the another post.
Hi ashmitp869 ,
I suggest you check that the DATESYTD('Date'[Date], "6/30") in your DAX is returning the date range you want. Normally, when you specify a year-end date in the DATESYTD function, that date is considered the new year-end date. Therefore, the function calculates the new year-start date from the day after the specified year-end date. For example, if you specify a year-end date of June 30, the new year-start date will be July 1. According to your needs, I recommend you to use DATESBETWEEN.
YTD - Billed(Ex-GST) TEST = VAR _maxDate = MAX(vFactBudgetActuals[Transaction Date]) VAR OldSystemRevenue = CALCULATE( [Revenue Amount] - SUM(vFactBudgetActuals[Cost Amount]), vFactBudgetActuals[Service ID] = "REVENUE", DATESBETWEEN('Date'[Date],DATE(2024,7,1),DATE(2025,2,21)) ) VAR NewSystemRevenue = CALCULATE( SUM(PJC[TOTAMTSR]), DATESBETWEEN('Date'[Date],DATE(2025,2,22),MAXX(ALL('Date'),'Date'[Date]))), PJC[CATEGORY] = "REVENUE" ) RETURN OldSystemRevenue + NewSystemRevenue——————————————————————————————————————————————————
If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.
Best regards,
Mengmeng Li
