Forum Discussion
Post ProdigySimplify and Optimize YTD Measure
Hello everyone!
We are working with a fiscal calendar in our reports.
That means that I can't use some of these awesome time intelligence functions since our fiscal months have specific start/ end dates.
Due to this "challenge", I use this approach to get the YTD Ration between sales and budget.
Ratio =
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] )
)
)
)
Do you think there is a much more efficient and elegant approach to get this ratio?
Is it useful to you some variables instead of the whole function?
Unfortunately, I get also values for the whole year (future).
How can I limit this measure just until today?
2 Replies
- Greg_Deckler
Community Champion
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
joshua1990thanks, 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
