Simplify and Optimize YTD Measure

[joshua1990]

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?

[Greg_Deckler]

@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-TIT...

[joshua1990]

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