Forum Discussion
YoY in incompleted year
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!
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
2 Replies
johnt75Super User
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 - lastYearTotalHi 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