YoY in incompleted year

[Anonymous]

Hi - 

Here is my expected result. 

Date	Sum	Sum (last year)	YoY diff
2019 Jun	150	-	-
2020 Jun	100	150	-50
2021 Jun	120	100	+20
2022 (Apr)	80	120	-40

My question is that how to create a measure to calculate YoY differences for 2022 when the year isn't completed yet? If I use dateadd/sameperiodlastyear function, power bi could only compare the value of 2021 Apr, which this data is absent (and it is not what I want).

Thanks a lot for your help!

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
var _1=MAXX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))),[Date])
return
IF(
    MONTH(MAX('Table'[Date]))=MONTH(_1),1,0)

Sum =
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&'Table'[Date]<=MAX('Table'[Date])),[amount])

Sum (last year) =
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))-1&&'Table'[Date]<=MAX('Table'[Date])),[amount])

YoY diff =
IF(
    [Sum (last year)]=BLANK(),BLANK(),
[Sum] - [Sum (last year)])

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

When the last date is 2022.4:

Add a row of data for 2022.5 and the last date as 2022.5:

The results also change with the date:

Please click here for the pbix file

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[johnt75]

You can create a measure like

YOY Diff =
var currentDate = MAX('Date'[Date])
var lastYearTotal = CALCULATE( [Total measure], REMOVEFILTERS('Date'), 'Date'[Year] = YEAR(currentDate) - 1)
var currentYearTotal = [Total measure]
return currentYearTotal - lastYearTotal