topic Re: YTD Prorated Target/Budget - Dax formula in DAX Commands and Tips

YTD Prorated Target/Budget - Dax formula

BILearner — Tue, 09 May 2023 15:13:14 GMT

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!

Re: YTD Prorated Target/Budget - Dax formula

johnt75 — Tue, 09 May 2023 15:29:47 GMT

I think you can use

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

Re: YTD Prorated Target/Budget - Dax formula

BILearner — Tue, 09 May 2023 15:39:17 GMT

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.

Re: YTD Prorated Target/Budget - Dax formula

johnt75 — Tue, 09 May 2023 15:43:46 GMT

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.

Re: YTD Prorated Target/Budget - Dax formula

BILearner — Tue, 09 May 2023 16:32:48 GMT

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.