Forum Discussion

AOD's avatar
AOD
Icon for Helper III rankHelper III
4 years ago
Solved

Calculate previous year total amount for current year

Hi All, Need help in Calculate  previous year total amount for current year . I have Year and current year amount in source. Need Previous year amount to be derived .   Year Current year Amoun...
Need Help
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, AOD 

     

    It is solved by calculation column. First calculate the year of the previous year, and then output the total amount of the previous year.

     

    Previous year = 
DATEADD ( 'Table'[Year], -1, YEAR )

    Previous year Amount =
CALCULATE (
    MAX ( 'Table'[Current year Amount] ),
    FILTER ( 'Table', 'Table'[Year] = EARLIER ( 'Table'[Previous year] ) )
)

     

     

    Best Regards,

    Community Support Team _Charlotte

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

AOD , If you need this as a new column

 

last year = sumx(filter(Table, [Year] = earlier([Year]) -1) , [Current year] )

 

for measure prefer to have separate year table(say Date) joined to this table 

 

 

 

Curr Year = CALCULATE(sum('Table'[THis year]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[THis year]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA