Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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?
Solved! Go to Solution.
@sean_cochran , Change this and try
// Find the cutoff for current data
VAR maxDate = CALCULATE(
MAX( Dates[Date] ),
ALL( Dates )
)
@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?
User | Count |
---|---|
134 | |
74 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
63 | |
63 | |
51 |