Forum Discussion
Simplify and Optimize YTD Measure
joshua1990 - Would need sample data as text and expected output to be more specific. However, you could maybe limit where you return a value using something like this:
Ratio =
VAR __Calc =
DIVIDE (
CALCULATE (
[Sales],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Fiscal Year]
= MAX ( 'Calendar'[Year] )
&& 'Calendar'[Date]
<= MAX ( 'Calendar'[Date] )
)
),
CALCULATE (
[Budget],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Fiscal Year]
= MAX ( 'Calendar'[Year] )
&& 'Calendar'[Date]
<= MAX ( 'Calendar'[Date] )
)
)
)
RETURN
IF(MAX('Calendar'[Date])>TODAY(),BLANK(),__Calc)
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
- joshua19906 years ago
Post Prodigy
thanks, Greg_Deckler : You approach works almost perfectly!
Somehow I need to limit the IF(MAX ... to the current week.
With your approach, the yearly average runs just until the last week.
I need to run the yearly average until this week. But how?
Something like IF ( MAX ( Fiscal Week) > current Week, Blank(), _Calc