Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 q...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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