YTD Prorated Target/Budget - Dax formula

[BILearner]

Hello! 
I have a Target table by Monthly grain in the following format which has a relationship with Date table via BridgeMonthYear table to resolve many to many relationship.

MonthYear	Target
Jan-23	64
Feb-23	57
Mar-23	32
Apr-23	58
May-23	52
Jun-23	56
Jul-23	58
Aug-23	45
Sep-23	56
Oct-23	49
Nov-23	50
Dec-23	48

I calculate Prorated Target as 

Total Target Prorated = 
VAR DayInContext = COUNTROWS(D_Calendar)
VAR DaysInMonth = CALCULATE(COUNTROWS(D_Calendar),ALL(D_Calendar),VALUES(D_Calendar[MonthYear]))
VAR MonthlyTarget = CALCULATE([Total Target])
Var Result = DIVIDE( DayInContext,DaysInMonth,0) * MonthlyTarget
RETURN
Result

 
I Calculate YTD Target as 

YTD Target = CALCULATE(
               [Total Target Prorated],
                  CALCULATETABLE(
                    DATESYTD(D_Calendar[Date]),
                    D_Calendar[FutureDate]=""Past""
                  )
)

YTD Target is not showing up expected result since the YTD Target performs following calculation at its core-

Total Target (till end of May) * (Day In Context/DayInMonth) 

From Table above
= 263 * (128/151) 
= 222.94
Where,
263 = Total Target as of May end. 
128 = Days so far in the year
151 = Day count till end of May

This is a problem because it is prorating target for each day so far in the year. 

Instead, I want it to perform calculation like this for YTD Target
Check if month has passed if yes then Total Target for that month(s) + prorated Target for the Month in Progress

Which would translate to following for the table posted above 

YTD Prorated Target = 

Jan Target + Feb Target + Mar Target + Apr Target + ( [May Target] * 8/31)

Where 8 = days in May so far 
& 31 = Total days in May

so the number would be, 
= 64 + 57 + 32 + 58 + (52 * 8/31)
= 224.42

The results are quite different by both methods (222 Vs 224). In PBI report I want to replicate logic/method 2 for YTD prorated Targets. How should I modify the Prorated Target or YTD Target formula to replicate method 2 logic?

Thanks!

[johnt75]

I think you can use

YTD Target =
SUMX (
    CALCULATETABLE (
        DATESYTD ( D_Calendar[Date] ),
        D_Calendar[FutureDate] = "Past"
    ),
    [Total Target Prorated]
)

[BILearner]

Thank you @johnt75 for you recommendation! I think this will work. 

Do you think the Daily Proration Formula can be adjusted in any way? I need to throw it in a card visual and the prorated total is not showing up as it should. I am trying to avoid creating extra calculated columns or summarized tables. 

[johnt75]

I think it should work if you put a relative date filter on the visual. Just set 'Date'[Date] to be in the current, or last, 1 day. Depends on whether you want to include today or end at yesterday.

[BILearner]

Relative date filter has no impact on Prorated Target. I think the Prorated Target formula needs to be changed for iteration. I tried to do that and got weird results. It needs to be similar to what you've done for YTD Target.