cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Change from previous year

Hi guys,

 

I am relatively new to Power Bi, and I am trying to work out the change between previous year for a dataset with years from 2017 to 2041

 

ASGSCode SexKey AgeGroupKey ERPYear Number 

125011473310020177923.5626292973
125011473310020188675.12833908559
125011473310020199869.29416907326
1250114733100202011288.4220074681
1250114733100202111780.4204141863
1250114733100202212344.0993552951
1250114733100202312739.5261897384
1250114733100202413056.1681337709
1250114733100202513607.7674308966
1250114733100202614187.0875408086
1250114733100202714730.8783805058
1250114733100202815283.5474685934
1250114733100202915827.4857965463
1250114733100203016402.0931198222
1250114733100203116971.9095157816
1250114733100203217559.8887122825
1250114733100203318122.5555377869
1250114733100203418691.4759445159
1250114733100203519265.3329322402
1250114733100203619850.5697318996
1250114733100203720448.4386043441
1250114733100203821032.7118328804
1250114733100203921547.1260050428
1250114733100204022057.0523590882
1250114733100204122565.0054992267

 

I have tried changing my year column 'ERPYear' to a date format, and attempted PREVIOUSYEAR, and SAMPLEPERIODLASTYEAR but I cant seem to get anything to work!

 

Help would be greatly appreciated - thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Change from previous year

Hi @d4rren 

 

You can create a Column as below.

Column = 
VAR __previousYear = 'Table'[ERPYear] - 1
VAR __previousYearNumber = 
CALCULATE(
    SUM( 'Table'[Number] ),
    ALLEXCEPT( 'Table', 'Table'[ASGSCode] ),
    'Table'[ERPYear] = __previousYear
)
RETURN 
IF( NOT ISBLANK( __previousYearNumber ), 'Table'[Number] - __previousYearNumber )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Change from previous year

Hi @d4rren 

 

You can create a Column as below.

Column = 
VAR __previousYear = 'Table'[ERPYear] - 1
VAR __previousYearNumber = 
CALCULATE(
    SUM( 'Table'[Number] ),
    ALLEXCEPT( 'Table', 'Table'[ASGSCode] ),
    'Table'[ERPYear] = __previousYear
)
RETURN 
IF( NOT ISBLANK( __previousYearNumber ), 'Table'[Number] - __previousYearNumber )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Change from previous year

@d4rren 

Try this with a separate year table

 

diff from last year
calculate(sum(Table[Number ]),filter(Year[Year]=max(Year[Year]))) -
calculate(sum(Table[Number ]),filter(Year[Year]=max(Year[Year])-1))
or
divide(calculate(sum(Table[Number ]),filter(Year[Year]=max(Year[Year]))),
calculate(sum(Table[Number ]),filter(Year[Year]=max(Year[Year])-1)) )

 

 

Or try these with dates and date calendar

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Change from previous year

@Mariusz this is exactly what I was after - so many ways of doing things in Power Bi!

Highlighted
Frequent Visitor

Re: Change from previous year

Thanks @amitchandak I will try this solultion, and accept if I can get it to work

 

thank you!

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors