cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## YTD Prorated Target/Budget - Dax formula

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!

4 REPLIES 4
Super User

I think you can use

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

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.

Super User

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.

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors