Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sshweky
Helper III
Helper III

Previous Years vs Current Year

Hi,

 

I want to compare current year's sales vs last year's sales. I am not 100% clear on how time intelligence features work.

 

I have a "Years" that has the year, year beginning date & year end date.

 

   PreviousYearSales = CALCULATE(sum(InvoiceDetail[TotalSales$]), PREVIOUSYEAR(Years[EndofYear]))

 

I am filter sales out for 2015 and the "PreviousYearSales" come out blank?!

 

Can some one sum up the logic on how this works?

 

Thanks,

 Steven

12 REPLIES 12
anakoom
Helper I
Helper I

I have simalar issue if you can help me with this

Can the same be accomplished when using a Multidimensional OLAP Cube?


@Hikmer wrote:

Can the same be accomplished when using a Multidimensional OLAP Cube?


 

I haven't used OLAP cubes in Power BI yet, so I can't answer in detail.

 

The only things that are required, as far as I know, are data with a date, a Dates or Calendar table with no missing dates, and a data model that joins these two. If you load the cube data into your Power BI data model and model the relationship to the Calendar table, I expect it would be treated the same.

 

The issue I am facing is similar to this post, if I add a filter for year thne I no longer see a year over year percentage calcualtion.  I beleive I need the previous year to still be exposed while still allowing for the visualization to "slice" on the year but not filter on the year so that previous year data can be calcualted.  See below

 

2017-08-08_13-36-50.png

 

 

Hello Hikmer,

 

It appears that you may have set the Subtotal value for the year to not show.  Check your Visualization properties.

 

Also, please post your function and full screenshot of the report page so that we can get a better idea of what you are working with.

 

Thanks!

Anonymous
Not applicable

Did you get it resolved? I have the same issue with blank....

I had this problem as discussed in this thread https://community.powerbi.com/t5/Desktop/SAMEPERIODLASTYEAR-with-a-year-filter/td-p/91501

 

What's happening is that your filter on the year is also making the previous year data unavailable. You need to use FILTER within your calculation to release the filter on the previous year's data and make it available to the calculation.

 

This is the calculation that worked for me.

 

TotalRevenueThisPeriodLY = CALCULATE(SalesDataAggregated[TotalRevenue], FILTER(ALL(Dates), Dates[CalendarYear] = MAX(Dates[CalendarYear])-1), SAMEPERIODLASTYEAR(Dates[Date]))

 

I was using SAMEPERIODLASTYEAR, but you can use whatever you need.

 

The important point is the CALCULATE on the outside and the FILTER(ALL(Dates), ...) as the second argument on the inside.

 

FILTER(ALL(Dates)...) is removing your 2015 filter temporarily for this calculation, and expanding it to the entire Dates table so that it can access and calculate the previous year's value.

 

 

DominikPetri
Advocate V
Advocate V

Hi Steven,

 

is your dates column (Years[EndofYear]) in your date dimension? Is the relationship to your fact table set up correctly?

Do you have a date dimension? Is the table marked as date table?

 

Without any knowledge how you set up your data model, help is quite difficult. Have a look here for more info on how to use PREVIOUSYEAR() with CALCULATE:

 

DAX Function Reference: PREVIOUSYEAR Function (DAX)

 

Best regards,

Dominik Petri.

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘

I believe you  might have better luck with DATEADD, -1, year) intead of previous year.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Be carefull with DATEADD!  It will only compare similar days between the two periods.  For instance, in the first year you only have records on 200 days and the current you have records on 260 days.  Using DATEADD will ignore the records that happened on days not present in the prior period.

Instead, use PARALLELPERIOD().  Same syntax as DATEADD without the nasty side effect.

Anonymous
Not applicable

Hi sshweky,

 

   can you please post an example file of data so I can help you?

Thanks

Kieran
Advocate II
Advocate II

Hi,

The following link looks very helpful to me.

http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx?...

 

If this does not answer the question for you please reply to this thread and I will try and provide a more clear answer by loading AdventureWorks into Power BI and providing example syntax.

 

Kind Regards,

 

 

Kieran.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.