Help with measures - when half of the data is in old system and rest in new System for One Project?

[ashmitp869]

Hi All,

I have a Projects (ABC001) whose where half of data (Jul 2024 to Feb 2025)is in old system and from Mar2025 the data will be in "NewSystem" .
How can I combine the two data to give the total value  for PTD - Billed (Ex- GST).

Please suggest.

I 

PTD - Billed(Ex-GST) = 
var _max = max(vFactBudgetActuals[Transaction Date])
RETURN

SWITCH(
SELECTEDVALUE(Projects[DataSource]),
"OldSystem",CALCULATE([Revenue Amount] - Sum(vFactBudgetActuals[Cost Amount]),vFactBudgetActuals[Service ID] = "REVENUE",
                         FILTER(SM,SM[Service Group Level 0] ="REVENUE"),
                         //FILTER(ALL('Date'),'Date'[Date] <= _max)
                         FILTER(all('Date'),'Date'[Date] <=max('Date'[Date]))
                         //FILTER('Date','Date'[Year] <=SELECTEDVALUE('Date'[Year]) && 'Date'[Month] <= SELECTEDVALUE('Date'[Month])
                         ) 

,
    "NewSystem",
    CALCULATE(SUM(PJC[TOTAMTSR]),            
            FILTER(all('Date'),'Date'[Date] <=max('Date'[Date]))            
            ,
            PJC[CATEGORY]  = "REVENUE"
        )
)

Regards 

[rajendraongole1 · Accepted solution]

Hi @ashmitp869  - I have modified the above measure, can you please try the below one.

PTD - Billed(Ex-GST) =
VAR _maxDate = MAX(vFactBudgetActuals[Transaction Date])

VAR OldSystemRevenue =
CALCULATE(
[Revenue Amount] - SUM(vFactBudgetActuals[Cost Amount]),
vFactBudgetActuals[Service ID] = "REVENUE",
FILTER(SM, SM[Service Group Level 0] = "REVENUE"),
FILTER(ALL('Date'), 'Date'[Date] <= _maxDate && 'Date'[Date] <= DATE(2025,2,28)) -- Up to Feb 2025
)

VAR NewSystemRevenue =
CALCULATE(
SUM(PJC[TOTAMTSR]),
FILTER(ALL('Date'), 'Date'[Date] <= _maxDate && 'Date'[Date] >= DATE(2025,3,1)), -- From Mar 2025 onward
PJC[CATEGORY] = "REVENUE"
)

RETURN
OldSystemRevenue + NewSystemRevenue

Hope it works. please check.

[ashmitp869]

Can you also help with the MTD and YTD expression as well

MTD - Billed(Ex-GST) = 
 SWITCH(
SELECTEDVALUE(Projects[DataSource]),
"OldSystem",CALCULATE([Revenue Amount] - Sum(vFactBudgetActuals[Cost Amount]),vFactBudgetActuals[Service ID] = "REVENUE") 
,
    "NewSystem",
    CALCULATE(
    TOTALMTD(
        SUM(PJC[TOTAMTSR]),
        'Date'[Date],
        FILTER(PJC, PJC[CATEGORY]  = "REVENUE")
    )
)
)

YTD - Billed(Ex-GST) = 

SWITCH(
SELECTEDVALUE(Projects[DataSource]),
"OldSystem",CALCULATE([Revenue Amount] - Sum(vFactBudgetActuals[Cost Amount]),vFactBudgetActuals[Service ID] = "REVENUE" ,
                        DATESYTD('Date'[Date],"6/30"),
                        FILTER(SM,SM[Service Group Level 0] ="REVENUE")) 

,
    "NewSystem",
    CALCULATE(
            SUM(PJC[TOTAMTSR]),
            DATESYTD('Date'[Date], "6/30"),
        FILTER(PJC, PJC[CATEGORY]  = "REVENUE")
    )
)

[rajendraongole1]

Hi @ashmitp869  - Your MTD (Month-to-Date) and YTD (Year-to-Date) calculations for Billed (Ex-GST) are mostly correct.

I have updated few please check the below:

MTD - Billed(Ex-GST) =
VAR _selectedDataSource = SELECTEDVALUE(Projects[DataSource])

RETURN
SWITCH(
_selectedDataSource,

"OldSystem",
CALCULATE(
[Revenue Amount] - SUM(vFactBudgetActuals[Cost Amount]),
vFactBudgetActuals[Service ID] = "REVENUE",
TOTALMTD(SUM([Revenue Amount]), 'Date'[Date]) -- Apply Month-to-Date filter
),

"NewSystem",
CALCULATE(
TOTALMTD(
SUM(PJC[TOTAMTSR]),
'Date'[Date]
),
PJC[CATEGORY] = "REVENUE"
)
)

for YTD:

YTD - Billed(Ex-GST) =
VAR _selectedDataSource = SELECTEDVALUE(Projects[DataSource])

RETURN
SWITCH(
_selectedDataSource,

"OldSystem",
CALCULATE(
[Revenue Amount] - SUM(vFactBudgetActuals[Cost Amount]),
vFactBudgetActuals[Service ID] = "REVENUE",
DATESYTD('Date'[Date], "6/30") -- Apply Year-to-Date filter
),

"NewSystem",
CALCULATE(
SUM(PJC[TOTAMTSR]),
DATESYTD('Date'[Date], "6/30"),
PJC[CATEGORY] = "REVENUE"
)
)

Hope this works at your end. please check and confirm.