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
sean_cochran
Helper II
Helper II

Conditional Annualization of Measure

I have a working measure [Turnover]. I want to create another measure [Turnover (Annualized)] that displays [Turnover] for complete years and the predicted full-year value of [Turnover] for the current incomplete year.

 

Current DAX:

 

Turnover (Annualized) = 

// Find the cutoff for current data
VAR maxDate = CALCULATE( 
    MAX( Dates[Date] ), 
    ALL( Dates[Date] )
)

// Extrapolate full year turnover based on number of days since start of year 
VAR annualizedTurnover = [Turnover] / YEARFRAC ( DATE ( YEAR( maxDate ), 1, 1 ) , maxDate, 1 )

// If maximum date in filter context = maxdate, then return annualized turnover, otherwise return [Turnover]
RETURN IF(
        MAX(Dates[Date]) = maxDate,
        annualizedTurnover,
        [Turnover]
    )

 

 

Current Attempt Output:

sean_cochran_1-1707860099547.png

 

The current attempt correctly calculates the annualized value. However, it displays incorrect values (off by a few decimal places) for complete years. 2022 and 2023 should have the exact same values for both [Turnover] and [Turnover (Annualized)]. I am confused. Why would the IF statement return different values from the [turnover] measure for these years? What am I missing?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@sean_cochran , Change this and try

 

// Find the cutoff for current data
VAR maxDate = CALCULATE(
MAX( Dates[Date] ),
ALL( Dates )
)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@sean_cochran , Change this and try

 

// Find the cutoff for current data
VAR maxDate = CALCULATE(
MAX( Dates[Date] ),
ALL( Dates )
)

Worked! Thank you so much! Although, I don't know why it worked. Shouldn't the ALL() function work with columns too?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.