Measure respect to the previous of a selected year

# Measure respect to the previous of a selected year

• Monday, May 16, 2011 11:07 AM

Hi.

I'm trying to determine the sum of Invoices for a selected year. I need to create a new measure respect to the previous selected year. Year is a dimension.

Any helps to do it, please? Many thanks

### All Replies

• Monday, May 16, 2011 4:13 PM

In order to get the aggregate from the prior year, you can use the PARALLELPERIOD function.  Something like:

```=CALCULATE(
SUM(FactResellerSales[SalesAmount]),
ALL(FactResellerSales),
PARALLELPERIOD(DimDate[FullDateAlternateKey], 1, YEAR)
)```

Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx
• Tuesday, May 17, 2011 9:42 AM

Hi Javier, but if I have only year and month in the fact table? Do I need to have to create a date and a time dimension? Thanks
• Tuesday, May 17, 2011 10:29 AM

Psorca,
All time intelligence functions in PowerPivot/DAX require a calendar table as mandatory. If you don't have such a table, time intelligence functions might return wrong values. Take a look at: http://www.powerpivotblog.nl/powerpivot-time-intelligent-functions-golden-rules
Otherwise, if you have only years and months, a simpler formula might be something like:
=IF (
COUNTROWS (VALUES (Fact[YEAR])) = 1,
CALCULATE (
SUM (Fact[Amount]),
Fact[Year] = VALUES (Fact[Year]) - 1
),
BLANK ()
)
Nevertheless, I strongly suggest you to take a look at the many sources on the web which describe how to implement time intelligence, adding a calendar table is strongly reccomended as it leads to a better user experience since you can handle quarters, fiscal years and so on.

• Marked As Answer by Tuesday, May 17, 2011 4:16 PM
•
• Tuesday, May 17, 2011 12:09 PM

Psorca,
You can easily create a date dimension with next expressions:
- in case of one column with data formatted like yyyymm

=DATE(LEFT([YearMonth],4),RIGHT([YearMonth]),1)
- in case of two columns with year and month
=DATE([Year],[Month],1)

Eddy N.
• Tuesday, May 17, 2011 4:16 PM

Hi,

also I think to create a date in the fact table and a date dimension, but I'd like to skip a passage because I need to load data already aggregated by year and month.

All your replies are good and interesting answers, but in this case I think that Alberto provides me the reply more useful.

Many thanks.