cancel
Showing results for
Did you mean:
Highlighted
Member

## 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

## Re: Previous Years vs Current Year

Hi,

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

Kind Regards,

Kieran.

Member

Hi sshweky,

Thanks

## Re: Previous Years vs Current Year

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.

Super User

## Re: Previous Years vs Current Year

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

Frequent Visitor

## Re: Previous Years vs Current Year

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

Regular Visitor

## Re: Previous Years vs Current Year

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.

Regular Visitor

## Re: Previous Years vs Current Year

I have simalar issue if you can help me with this

Regular Visitor

## Re: Previous Years vs Current Year

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

Regular Visitor

## Re: Previous Years vs Current Year

@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.