Skip to main content
cancel
Showing results for 
Search instead 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

Reply
BILearner
Advocate I
Advocate I

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.

MonthYearTarget
Jan-2364
Feb-2357
Mar-2332
Apr-2358
May-2352
Jun-2356
Jul-2358
Aug-2345
Sep-2356
Oct-2349
Nov-2350
Dec-2348

 

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
johnt75
Super User
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. 

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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