TIPS: How to calculate YTD YoY on a table which ends before your general Time table.

[Stemar_Aubert]

In the backlog of functions I wanted to clean, I had a YTD YoY calculation method which I use often.

The issue is the following. If you handle various data sets, some of those will end up in September while others keep going until December. This creates a challenge, as using DATESYTD will always yield the latest row of your time table, and repeat the last rows calculated, forcing you to either filter manualy on a time range the visualsation, or find a workaround.

So, in order to frame your YTD YoY, you must introduce LASTNONBLANK somewhere.

Here's how I did it:

YTD 18 =
CALCULATE (
    'Table'[Values],
    SAMEPERIODLASTYEAR ( DATESYTD ( 'Time'[DATES] ) ),
    SAMEPERIODLASTYEAR ( LASTNONBLANK ( 'Table'[DATES], 'Table'[DATES] ) )
)

YTD 19 =
CALCULATE (
    'Table'[Values],
    DATESYTD ( 'Time'[Dates] ),
    LASTNONBLANK ( 'Table'[DATES], 'Table'[DATES] )
)

Then your YoY is simply YTD 19 - YTD 18.

When you put this in a table, it will play nicely and stop calculating the measure when needed.

Hope it helps !

[v-lid-msft]

Hi @Stemar_Aubert ,

Thank your for sharing this wonderful tips which will benefit more users, does the result should looks like following? 

If you have any other questions about this scenario, please kindly ask here and we will try to resolve it.

Best regards,

[Stemar_Aubert]

Hi @v-lid-msft  !

Here is what it looks like.

Measure A doesn't have the time-bound paramenter, whereas Measure B does.

You can see how PowerBi calculates the same value for October, November and December. Measure B instead, yields empty rows, which is the desired behavior in this case.

In a linechart, you then avoid this problem :

Hope this helps !

Cheers